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 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 regarding to each other in one batch. This MAY NOT be true when there is complex query in issue. For most of the time, it is will be same.

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

About these ads

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

  1. Pingback: Ajax Database Best Practices Joey JavaScript

  2. 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 ?.

    Like

  3. 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

    Like

  4. 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?

    Like

  5. 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.

    Like

  6. 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.

    Like

  7. 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?

    Like

  8. 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

    Like

  9. 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.

    Like

  10. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  12. 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

    Like

    • 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

      Like

  13. Pingback: SQL SERVER – Server Side Paging in SQL Server 2011 – A Better Alternative Journey to SQL Authority with Pinal Dave

  14. 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

    Like

  15. Pingback: SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

  16. 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 |
    ——————-

    Like

    • 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

      Like

  17. 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

    Like

  18. 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 :)

    Like

  19. 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)

    Like

  20. Hi Pinal
    Is there any example where cte is used to improve the performance of a query..generally in most cases using cte gives bad performance right..

    Like

  21. Is there a way to include a UNION between more than one table in the cte, and perform ordering so that paging with the row_number will work

    Example – This works with one table.

    With cteresult as
    (Select col1,
    col2,
    col3,
    Row_Number() OVER(Order by col1) as RowNumber
    from table1)
    select * from cteresult
    where RowNumber between 1 and 10

    Hoe can I include a union with another table, but the ordering must be across both tables.

    Like

  22. Possible! but you must used subquery technique..

    select Row_Number() OVER(Order by col1) as RowNumber, col1, col2
    from
    (
    select col1, col2 from table1
    union
    select col1, col2 from table2
    ) as tmpTable
    where RowNumber between 1 and 10

    How this works?

    First, we retrieved all the records in one or more tables and put it into a temporary table
    Second, select the records from the temporary table and perform Row_Number

    AND Mark Smit, SEE MY POST ABOVE (stone October 20, 2011 at 3:57 PM)
    just change the query in the declare cursor keyset…that technique is far more efficient than cte when it comes to large amount of record…

    NOTE: if one or both tables containing large amount of records, the query will be slower ..

    If you need help and more info about it just leave a comment here.

    Like

  23. Pingback: SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

  24. Pingback: SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

  25. how to do a paging from infinite looping CTE?

    for example i have a recurrence schedule without an end date :

    ;WITH cteDate as
    (
    (
    select event_id, summary, dt_start
    from events
    )
    UNION ALL
    (
    select event_id, summary, dateadd(d,1,dt_start)
    from cteDate
    )
    )

    SELECT * FROM cteDate
    order by dt_start desc
    OPTION(MAXRECURSION 0)

    Thanks

    Like

  26. I need help also about paging and using UNION ALL for multiple tables:

    How do i implement an optimized paging when joining multiple tables using UNION ALL and returning only specific number of rows…
    ——————————————————————————————————————-
    declare @startRow int
    declare @PageCount int

    set @startRow = 0
    set @PageCount = 20

    set rowcount @PageCount

    select Row_Number() OVER(Order by col1) as RowNumber, col1, col2
    from
    (
    select col1, col2 from table1 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table2 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table3 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table4 where datetimeCol between (@dateFrom and @dateTo)
    union all
    select col1, col2 from table5 where datetimeCol between (@dateFrom and @dateTo)
    ) as tmpTable
    where RowNumber > @startRow
    ——————————————————————————————————————-

    table 3, 4, & 5 have huge number of row (millions of rows) where table 1 & 2 may only have few thousand rows.

    If startRow is “0”, I only expect data from Row 1 to 20 (from Table1).
    I’m getting the correct result but has a high overhead on the remaining table while sql server tries to all all the data and filter it….

    the longer the interval of the @dateFrom and @dateTo makes my query significantly slower while trying to retrieve only few rows from the overall result set

    Please help how i can implement a simple but better approach with a similar logic. :(

    Like

  27. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s