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 (http://blog.SQLAuthority.com)

About these ads

25 thoughts on “SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

  1. Pingback: SQL SERVER - 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) - CTE vs. Derived Table Journey to SQL Authority with Pinal Dave

  2. 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,

    Like

  3. 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.

    Like

  4. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  5. 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?

    Like

  6. 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(

    Like

  7. Hello Tarun,

    Even your question is not much clear but I think you are asking to get the resultset in pages of fixed size to display one by one.
    At SQL Server side there is nothing special for paging and you will have to use the method explained in this page:

    http://blog.sqlauthority.com/2007/04/03/sql-server-t-sql-paging-query-technique-comparison-sql-2000-vs-sql-2005/

    But LINQ have some functions like Skip & Take to use for paging purpose.

    Regards,
    Pinal Dave

    Like

  8. 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

    Like

  9. 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.

    Like

  10. 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

    Like

  11. 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….

    Like

  12. 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!!!

    Like

  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  14. 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

    Like

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s