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. Below is each paging query, run against AdventureWorks.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
21 Comments. Leave new
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