Paging in SQL Server 2005

re: Paging in SQL Server 2005

max — Tue, 28 Apr 2009 20:59:33 GMT

Great article. Thanks

re: Paging in SQL Server 2005

daryl2000 — Wed, 14 Jul 2010 15:48:16 GMT

Thank you for posting this article, which greatly facilitated the development of a paging sproc. While it was easy to incorporate the ROW_NUMBER() function, SQL 2005 did not allow me to use a variable with TOP or a order by clause in the subquery. I tried to use a variable with SET ROWCOUNT in lieu of TOP, but the results were erroneous (due to the lack of the order by clause in the subquery). Based upon the query in the article, the paging sproc would be:

CREATE PROCEDURE dbo.PagedResults

@PAGE_SIZE bigint,

@PAGE_NUM bigint

BEGIN

SET NOCOUNT ON;

SELECT x.SomewhatLargeString

FROM (

SELECT A.SomewhatLargeString, ROW_NUMBER() OVER(ORDER BY A.SomewhatLargeString) AS TheCount

FROM #BigTableOfStrings A

) x

WHERE x.TheCount > ((@PAGE_NUM - 1) * @PAGE_SIZE) AND x.TheCount <= (@PAGE_NUM * @PAGE_SIZE)

ORDER BY x.TheCount

END

Without the use of dynamic SQL, could the sproc be tweaked to improve performance? Thanks.

re: Paging in SQL Server 2005

Adam Machanic — Wed, 14 Jul 2010 20:14:56 GMT

Hi Daryl2000,

There's no reason you shouldn't be able to use TOP + ORDER BY in a derived table. That's been allowed for several versions (at least since 7.0). Not sure what's going on there. However, in the time between when I originally wrote the post and now I've played a lot with this technique and have seen no differences between performance with and without the TOP + ORDER BY -- so it's not needed in any case.

Your query looks good to me as-is. I don't think there is any improvement to be had.