Wow... lemme say that again... WOW! SQL Server 2005 sucks for this! Yeah... it was a real world example.... In the real world, we have SQL Server 2000 and we had to use dynamic SQL because there is no programmable TOP in 2k (as all of you know). Our server is the Enterprise Edition running on 16 3.2 GHz CPU's and enough memory to choke a horse with some awesome harddisks.

I ran the TOP() code on my (humble) desktop machine at the 4000th 100 line page and never did let it finish. I run dynamic sql using just plain ol' EXEC and concatenating the values for page size, etc in, and BLAM!!! It runs in only a half a second on my desktop... talk about parameter sniffing... 9000th page takes 1.2 seconds. Of course, my desktop box is no match for the server at work.

Also, I made a couple of mistakes modifying the code for the forum... I left CustID in the ORDER BY instead of using RowNum... And, I used SELECT * instead of RowNum.

Here's the code I just used...

[font="Courier New"]

set statistics time on

--===== Declare the local variables

DECLARE @PageSize INT --How many rows to appear per page

DECLARE @PageNum  INT --What page number to appear

DECLARE @Skip     INT --Working variable for where to start for page

DECLARE @SQL      VARCHAR(8000)

--===== Set the local variables for pagesize and page

     -- PageSize and PageNum would be parameters in a stored proc

    SET @PageSize = 100

    SET @PageNum  = 9000

    SET @Skip    = @PageSize*@PageNum

--SELECT t.RowNum

--   FROM dbo.Test t,

--        (

--         SELECT TOP (@PageSize) RowNum

--           FROM dbo.Test WITH (NOLOCK)

--          WHERE RowNum NOT IN (SELECT TOP (@Skip) RowNum

--                                 FROM dbo.Test

--                                ORDER BY RowNum)

--          ORDER BY RowNum

--         ) d

--  WHERE t.RowNum = d.RowNum

SET @SQL = '

SELECT t.RowNum

   FROM dbo.Test t,

        (

         SELECT TOP '+STR(@PageSize)+' RowNum

           FROM dbo.Test WITH (NOLOCK)

          WHERE RowNum NOT IN (SELECT TOP '+STR(@Skip)+' RowNum

                                 FROM dbo.Test

                                ORDER BY RowNum)

          ORDER BY RowNum

         ) d

  WHERE t.RowNum = d.RowNum'

EXEC (@SQL)

set statistics time off[/font]

And, get this... on my box, running the same dynamic code on 2k instead of 2k5 only took about half the time... what did they do to the bloody optimizer in 2k5? And, yes, I'm running sp2 on 2k5 although I don't have anything beyond that (was holding out for sp3).