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

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)

Quest

SQL Download, SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – 2005 – Performance Dashboard Reports
Next Post
SQL SERVER – Fix: HResult 0x274D, SQLCMD Level 16, State 1 Error: Microsoft SQL Native Client : Login timeout expired

Related Posts

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

    Reply

Leave a Reply