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.
Reference: Pinal Dave (https://blog.sqlauthority.com), SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005
29 Comments. Leave new
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..
Just want to follow up..
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.
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.
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
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. :(