I was doing paging in SQL Server 2000 using Temp Table or Derived Tables. I decided to checkout new function ROW_NUMBER() in SQL Server 2005. ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. I have compared both the following query on SQL Server 2005.
SQL 2005 Paging Method
USE AdventureWorks GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 120 SET @EndRow = 140SELECT 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 2000 Paging Method
USE AdventureWorks GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 120 SET @EndRow = 140CREATE TABLE #tables (RowNumber INT IDENTITY(1,1), FirstName VARCHAR(100), LastName VARCHAR(100), EmailAddress VARCHAR(100)) INSERT INTO #tables (FirstName, LastName, EmailAddress) SELECT PC.FirstName, PC.LastName, PC.EmailAddress FROM Person.Contact PC ORDER BY FirstName, LastName, EmailAddressSELECT FirstName, LastName, EmailAddress FROM #tables WHERE RowNumber > @StartRow AND RowNumber < @EndRow DROP TABLE #tables GO
While running both the query at same time in query analyzer and comparing execution plan I have discovered that SQL 2005 query method cost is 46% and SQL 2000 query method cost is 54%. Looking at client Statistics I noticed significant improvement in Time Statistics.
Update (6/11/2007) : Extention of this article is published SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
Reference : Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
send me the interview questions
i am pretty much interested in the interview questions and answers
You rock!
you’re freaking amazing!!! give me more of your sweet sweet sql juice!
Pinal,
You insights are great and valuable. Please remove the post(s) whoever bugging and asking you about interview questions. They need to start contribution and add value to the group and not just bug you.
Thanks,
can anybody send me some interview question
Can sombody tell me about triggers in detail with example?
send me the interview questions.
Hi Pinal Dave,
Can you Please tell me what the difference between in Sql Sever 2005 and Sql Server 2000. Means what new enhancement is done in sql Server 2005 as compared to sql server 2000.(In brief)
Thanks in Advance.
Thanks for the excellent article! I was wondering what kinds of performance implications there are with using ROW_NUMBER for pagination, if any. I have searched for a few days on Google, and haven’t found much. I would like to believe that this function is optimized by SQLServer 2005. What kinds of impacts are there to the server? Is this query resource intensive for very large tables, or many concurrent users?
Would it not improve performance if you added “TOP (@EndRow)” to the RowNumber select statement? On very large tables (1,000,000 plus rows) the size of the Derived Table would be limited to the @EndRow size.
FROM ( TOP (@EndRow)
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
Thanks,
It’s excellent article.
is there any way paging technique in SQL Server 2008 ?
I thought SQL Server didnt allow referencing a column alias in a where clause..?
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
oh sorry just noticed the position of the where clause :) thx
i need to understand more on query processor as one of the new features of Microsoft SQL Server 2005. i would be happy if you could talk about its defination, role,pros and cons as well as query processing.
thanks for your great articles….
with rowdisplay as
(
SELECT FName, LName, Email,
ROW_NUMBER() OVER( ORDER BY PC.FName, PC.LName, PC.Email ) as ROWNUMBER
FROM person.contact PC
)
select * from rowdisplay where rownumber between 15 and 20
hello sir…
my name is sagar singh and i m doing ANIIT from Delhi institute. i hve learned sql server 2005 in niit course. One thing in my mind who is troubling me that how to improve to sql bez i think that niit’s book is not very good and when i search sql base job then i found that every organization wants sql with any language like c#,asp.net,java etc. so i wnt to ask u that it’s necessary to knw the language.
plz reply me as soon as sir….
I am new to sql and I am in trouble of fetching data from sql server using paging method.
I have one table having Primary column,DateModified and other fields. table having 4lac records.
I want to implement paging using sql server.
Conditions:
1] First column must be row number.
2] Latest modified records must be come first using DateModified columns
3] Perfomance must be high.
I have problem in query.
Suppose, I use
select * from
(Select row_number() over(order by SoldProductId) as rowNum,SoldProductId, OtherId,DateModified
From table1 order by DateModified desc
) as TempTable
where rowNum between 1 and 10
query. Then due to order by clause this query doest work.
Suppose, I use
select * from
(Select row_number() over(order by SoldProductId) as rowNum,SoldProductId, OtherId,DateModified
From table1
) as TempTable
where rowNum between 1 and 10
query.It doesnt returns latest records first.
How Can I solve this problem.
Please Help!!!
Dear all,
I Create Store proc paging.another store, i use it.How do you feel when I use it?
CREATE PROC [dbo].[sp_Paging_New]
(
@element NVARCHAR(MAX),
@nameFrom VARCHAR(1000),
@pk VARCHAR(1000),
@cond NVARCHAR(4000),
@groupBy NVARCHAR(1000),
@nameOrderBy NVARCHAR(1000),
@CurrentPage INT,
@PageSize INT
)
AS
DECLARE @SQL NVARCHAR(MAX)
DECLARE @FromIndex INT
DECLARE @ToIndex INT
BEGIN
SET @FromIndex = @PageSize * (@CurrentPage – 1) + 1
SET @ToIndex = @PageSize * @CurrentPage
IF (@PageSize IS NULL OR @PageSize < 1) SET @PageSize = 1
IF (@CurrentPage IS NULL OR @CurrentPage < 0) SET @CurrentPage = 0
IF(@cond”)
SET @cond = ‘ where ‘+@cond
IF(@groupBy”)
SET @groupBy = ‘ group by ‘+@groupBy
IF(@nameOrderBy”)
SET @nameOrderBy = ‘ ORDER BY ‘+@nameOrderBy
SET @SQL = ‘SELECT * ‘
SET @SQL = @SQL + ‘ FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ‘+@pk+’) AS RowNumber, ‘
SET @SQL = @SQL + ‘ COUNT(0) OVER() AS TotalRecord,’ + @element
SET @SQL = @SQL + ‘ FROM ‘+@nameFrom + @cond + @groupBy+’ ) AS TableName ‘
SET @SQL = @SQL + ‘ WHERE RowNumber BETWEEN ‘+Convert(nvarchar(10),@FromIndex)+’ AND ‘+Convert(nvarchar(10),@ToIndex)+’ ‘
SET @SQL = @SQL + @nameOrderBy
–PRINT @SQL
EXEC (@SQL)
END