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
[...] 9) If retrieving multiple rows, always use database paging techniques to retrieve only the rows which are necessary (displayed on screen). Click here for more information on how to do this [...]
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 ?.
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
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?
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.
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.
Check out my blog for a solution to fetching the last page efficiently.
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?
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
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.
[...] SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived … [...]
[...] Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query. (Read More Here) [...]
Great Query..
I think u’r master of Query.. hahaha
Thank u very much… :)
Hi Pinal,
Great query…
Fantastic..it works perfactly…
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
Hi Mark,
The condition “AND row_number=21″ in WHERE clause is not clear. Can you provide the full statement?
Regards,
Pinal Dave
tnx , i do it by your Sample
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…
http://frugalcoder.us/post/2010/02/23/tsql-paged-result-sproc.aspx
[...] is the blog article where I wrote about SQL Server 2005/2008 paging method SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Deri…. One can achieve this using OVER clause and ROW_NUMBER() [...]
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
Thanks however what about the tables need to be join?
Shyam
[...] SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Deri… [...]
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 |
——————-
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
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
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 :)
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)
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.
[...] SQL Server Interview Questions and Answers ISBN: 1466405643 Page#109-112 Common Table Expression (CTE) and Few Observation Multiple CTE in One SELECT Statement Query Delete Duplicate Rows Simple Example of Recursive CTE SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table [...]
[...] More on CTE: Simple Example of Recursive CTE Multiple CTE in One SELECT Statement Query Common Table Expression (CTE) and Few Observation Delete Duplicate Rows Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table [...]
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