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 = 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 2000 Paging Method
USE AdventureWorks GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 120 SET @EndRow = 140
CREATE 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, EmailAddress
SELECT 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
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