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