SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

I have received few emails and comments about my post SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005. The main question was is this can be done using CTE? Absolutely! What about Performance? It is same! Please refer above mentioned article for the history of paging.

SQL 2005 Paging Method Using Derived Table

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

SQL 2005 Paging Method Using CTE

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

Following Image of Execution Plan displays that the performance for both of them is same with regard to each other in one batch. This MAY NOT be true when there is a complex query in issue. For most of the time, it is will be same.

SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table SQL_CTEDerivedTable

Reference: Pinal Dave (https://blog.sqlauthority.com), SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL Function, SQL Joins, SQL Paging, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Fix : Error : An error has occurred while establishing a connect to the server. Solution with Images.
Next Post
SQL SERVER – LEN and DATALENGTH of NULL Simple Example

Related Posts

29 Comments. Leave new

  • K. Shiva Prakash
    July 9, 2007 10:40 pm

    Hello, Pinal ,

    I am facing one problem. I did the paging is MSSQL 2005. and now i binded it to the controls ( returned datasets). When i clicked on the next page then, is i have to come back to the sqlserver with new startrow and endrow values ?.

    Reply
  • Hi pinal

    Thanks for your valuable suggestion about paging. I am very thankful for that.

    Please suggest some good method to do paging for sql server 2000.

    thanks
    vijay

    Reply
  • So it’s creating a Derived Table, almost like a temp table and creating a row number column?

    What happens if you have to page through 50,000 rows???

    First it will create a massive 50,000 row Derived table – with the row number column, then you select from the Derived table.

    Can’t see that being good for memory or cpu performance.

    Any other solutions?

    Reply
  • You could always use “top” to limit the number of rows returned by the subquery…then you would only have the massive derived table if you actually paged to the end of the data.

    Reply
  • Arnab Mukherjee
    February 7, 2008 3:47 pm

    The code is pretty simple. But the problem is that it sorts the data that is coming out. And OVER() method needs an “order by” in it’s parameter. So if we want the data as it is in the database it is not possible in this way. If there is any way to eliminate the sorting then i think the code will be more appropriate.

    Reply
  • Check out my blog for a solution to fetching the last page efficiently.

    Reply
  • Hi Pinal,

    I have a table with 2 million records, i have created a Full text Index on a nvarchar column ‘itemName’ of size 70. When i do a query like:

    WITH ListTemp AS (Select ROW_NUMBER() OVER( ORDER BY [main].[itemName] ASC) as rownum,
    main.itemname,main.itemdescription
    FROM
    CONTAINSTABLE([ItemTemp1], itemname, ‘chevrolet’) AS A
    join [ItemTemp1] main on a.[key] = main.id
    )
    SELECT * FROM ListTemp WHERE rownum > 0 AND rownum <11 ORDER BY rownum

    it takes more than a minute to execute.

    While a query like:

    select main.itemname,main.itemdescription
    FROM CONTAINSTABLE([ItemTemp1], itemname, ‘chevrolet’) AS A
    join [ItemTemp1] main on a.[key] = main.id

    executes in a few seconds.

    Will not a full text search perform well when placed in a CTE?

    Reply
  • The post is very usefull and so much thanks.
    here i have one more doubts that how I can send criteria (dynamic) to this qeury. I want to show the result by page. But the result for some particular conditions. If you have any idea.. Please give me some shot.

    thanks again

    Reply
  • Note that because of the inner ORDER BY clause the entire table will need to be sorted prior to the CTE with it’s ROW_NUMBER value being used. This is a good time to have an alternate index on the columns you’re ordering by. :)

    You can verify this by examining the execution plan before and after adding the alternate index. Don’t forget to do a ALTER INDEX {alternateindexname} ON {tablename} REBUILD prior to examining the plan so that SQL Server has fresh statistics to work with.

    Reply
  • Hi Pinal,

    Great query…

    Fantastic..it works perfactly…

    Reply
  • Hi Pinal,
    I’m using the ROW_NUMBER() and OVER in my DSQL statement for a “paging” technique. I’m doing this because of large data sets.

    In the SELECT WHERE, I use: row_number>=1 AND row_number=21 AND row_number<=40, there maybe no row numbers in that range, but records do exist to be displayed.

    1st record set:
    1
    2
    4
    5
    7
    8
    13
    15
    19
    (9) rows

    2nd record set may only return 5 rows but there are many more:
    21
    22
    24
    25
    28

    ROW_NUMBER doesn't work as I'd anticipated, where I want to get the next 20 records and append them to the table display object.

    Hopefully, that makes sense.
    Mark

    Reply
    • Yes the same things Happen to me here is the Query
      declare @Startin int
      declare @Endin int
      set @Startin=0 ;
      set @Endin=20;
      with mytbl as
      (
      select Acm.AcName,acm.ShortAcName,acm.Address1,ROW_NUMBER()over(
      order by Acm.AcName,acm.ShortAcName,acm.Address1
      )as RowNum from tbl_AcMaster as Acm)
      select AcName,ShortAcName,Address1 from mytbl
      where rowNum>@Startin and rownum<@Endin
      order by AcName,ShortAcName,Address1
      go

      Reply
  • Hi Mark,

    The condition “AND row_number=21” in WHERE clause is not clear. Can you provide the full statement?

    Regards,
    Pinal Dave

    Reply
  • tnx , i do it by your Sample

    Reply
  • Michael J. Ryan
    May 13, 2010 9:49 am

    Just wanted to point out a recent post I made on this topic, it discusses how this works, as well as a broader example for extracting paged result sets…

    Reply
  • Hey guys based the above query i have come up with a genric idea of fetching data from any table..

    check this out .
    please ppl do let me know if there is any problem or it works for you like with me … enjoy..

    Create PROCEDURE [dbo].[GetData]
    (
    @Pagesize int,
    @Pagenumber int,
    @Tablename nVarchar(50),
    @ColumnList nVarchar(500),
    @Orderby nVarchar(50)
    )
    AS
    begin
    Declare @FirstRow nVarchar(20)
    Declare @LastRow nVarchar(20)
    Declare @query nVarchar (2000)

    SET @Pagenumber=@Pagenumber-1
    Select @FirstRow = @Pagenumber * @Pagesize + 1,
    @LastRow = @PageNumber * @PageSize + @PageSize ;

    set @query =’WITH cte_Table(PageNumber,’ +@ColumnList +’ ) AS ( SELECT ROW_NUMBER() OVER (ORDER BY ‘+@Orderby+’ ASC ) AS PageNumber,’+@ColumnList+’ FROM ‘+ @Tablename+’ ) SELECT * FROM cte_Table WHERE PageNumber Between ‘+@FirstRow +’ and ‘+ @LastRow
    print @query
    EXEC sp_executesql @query
    END

    Reply
  • Thanks however what about the tables need to be join?
    Shyam

    Reply
  • AbhIShek Online4all
    August 8, 2011 10:22 am

    Hi Dave,
    Is it possible to get the result, with out using loop?
    Or suggest me any easiest way to achieve the result.

    Thanks in advance,

    create table #tbl (id int , c1 varchar(10), c2 varchar(10), c3 varchar(10))
    insert into #tbl

    select 1 , ‘c’ , ‘d’ , ‘e’ union all
    select 2 , ‘d’ , ‘e’ , ‘f’ union all
    select 3 , ‘d’ , ‘f’ , ‘g’

    select * from #tbl
    drop table #tbl

    –Result:
    ——————-
    –| c1 | c2 |c3 |
    –|—-|—–|—-|
    –| cdd| def |efg |
    ——————-

    Reply
    • AbhIShek Online4all
      August 12, 2011 12:38 pm

      create table #tbl (id int , c1 varchar(10), c2 varchar(10), c3 varchar(10))
      insert into #tbl

      select 1 , ‘c’ , ‘d’ , ‘e’ union all
      select 2 , ‘d’ , ‘e’ , ‘f’ union all
      select 3 , ‘d’ , ‘f’ , ‘g’

      select * from #tbl

      declare @c1 varchar(100) , @c2 varchar(100) , @c3 varchar(100)
      select @c1 = ” , @c2=” , @c3=”
      select @c1 = @c1+c1 , @c2=@c2+c2 , @c3=@c3+c3 from #tbl
      select @c1 c1 , @c2 c2 , @c3 c3

      drop table #tbl

      Thanks,
      abhIShek onlin4all

      Reply
  • AbhIShek Online4all
    August 12, 2011 12:37 pm

    create table #tbl (id int , c1 varchar(10), c2 varchar(10), c3 varchar(10))
    insert into #tbl

    select 1 , ‘c’ , ‘d’ , ‘e’ union all
    select 2 , ‘d’ , ‘e’ , ‘f’ union all
    select 3 , ‘d’ , ‘f’ , ‘g’

    select * from #tbl

    declare @c1 varchar(100) , @c2 varchar(100) , @c3 varchar(100)
    select @c1 = ” , @c2=” , @c3=”
    select @c1 = @c1+c1 , @c2=@c2+c2 , @c3=@c3+c3 from #tbl
    select @c1 c1 , @c2 c2 , @c3 c3

    drop table #tbl

    Reply
  • Creating row number requires dbms to pull all the records..(imagine 1 million records)

    I created a script to make the trick… (note it will take time for first run)

    declare @Result table (
    ACCOUNTNO varchar(20),
    CONTACT varchar(40))

    declare curO cursor KEYSET for
    select ACCOUNTNO, CONTACT
    from CONTACT
    order by RecID

    open curO

    declare @ACCOUNTNO varchar(20)
    declare @CONTACT varchar(40)

    declare @PageSize int
    set @PageSize = 20 — CHANGE THE DESIRED PAGE SIZE
    declare @PageNo int
    set @PageNo = 200– CHANGE PAGE NUMBER TO GET
    declare @RecLoc int
    set @RecLoc = (@PageSize * (@PageNo-1)) + 1

    declare @CurrRow int
    set @CurrRow = 0

    fetch absolute @RecLoc from curO into @ACCOUNTNO, @CONTACT
    while @@FETCH_STATUS = 0 begin
    set @CurrRow = @CurrRow + 1
    insert into @Result (ACCOUNTNO, CONTACT)
    values (@ACCOUNTNO, @CONTACT)
    if @CurrRow >= @PageSize goto g
    fetch next from curO into @ACCOUNTNO, @CONTACT
    end
    g:
    close curO
    deallocate curO

    select * from @Result

    ENJOY!!!!!!!!
    You may send email at [email removed] for questions :)

    Reply
  • Additional information…
    1. The above temporary table (@Result) is temporary table resides in memory not in temp database
    2. The Cursor (curO) object can have any select query as long as MS SQL can recognized.
    3. Cursor object does not read the entire table once executed, instead it will position the cursor to the first record and wait for the next fetch next
    4. The heart of this is the FETCH ABSOLUTE n…you can instruct dbms to go into desired absolute position. :-)
    5. FETCHing is slower than any select statement but for few records (such as 2000 rows) this should be reasonably/exceptionaly fast enough
    6. I have tested this with about a 100K records and it retrieves the desired records in about less than 2.5 sec for first run…and a fraction of a seconds in the succeding runs! (MS SQL 2008, Windows 7, Intel Core 2 duo @2.93Ghz, 2 GB memory)

    Reply

Leave a Reply