When it comes to paging in SQL Server, there are various methods that can be used. In the past, developers often relied on temporary tables or derived tables to achieve paging functionality. However, with the introduction of SQL Server 2005, a new function called ROW_NUMBER() was introduced, which provides a more efficient and elegant solution for paging.

The ROW_NUMBER() function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. This function can be extremely useful when you need to retrieve a specific range of rows from a large dataset.

Let’s compare two queries, one using the SQL Server 2005 ROW_NUMBER() function and the other using the traditional SQL Server 2000 paging method.

SQL Server 2005 Paging Method

USE AdventureWorks;
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;

SQL Server 2000 Paging Method

USE AdventureWorks;
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;

By comparing the execution plans and client statistics of both queries, it was found that the SQL Server 2005 query method had a cost of 46%, while the SQL Server 2000 query method had a cost of 54%. Additionally, there was a significant improvement in time statistics when using the ROW_NUMBER() function.

The ROW_NUMBER() function provides a more efficient and concise way to implement paging in SQL Server. It eliminates the need for temporary tables or derived tables, resulting in improved performance and readability of the code.

Overall, the introduction of the ROW_NUMBER() function in SQL Server 2005 has greatly simplified the process of implementing paging functionality. It is recommended to utilize this function whenever possible to optimize your SQL Server queries.

For more information and an extended comparison of different paging techniques, you can refer to the article “SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table.”