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

Solarwinds
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

Solarwinds
, , , ,
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

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

    Reply
  • Just want to follow up..

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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. :(

    Reply

Leave a Reply

Menu