SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

17 years ago

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

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)

SQL Download, SQL Scripts, SQL Stored Procedure

SQL SERVER – 2005 – Performance Dashboard Reports

SQL SERVER – Fix: HResult 0x274D, SQLCMD Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired

21 Comments. Leave new

Dang
April 10, 2013 9:50 am
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
Reply