My Technical Notes

Friday, 8 March 2013

SQL Server Paging

SQL Server 2011 and above have built-in paging constructs. Unfortunately SQL Server versions lower than this do not have built-in paging.

Therefore we have to do the paging ourselves. Dave Pinal's 2000 vs 2005 Paging Article shows how a SQL expert does paging in SQL 2000 and SQL 2005.

Another article by Dave Pinal shows that derived tables and CTE are the same when when it comes to performance in SQL Server 2005. See 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

On the example of how to do paging in SQL Server 2005, Dave Pinal gives the following example code:


USE AdventureWorks
GO
DECLARE  @StartRow INT ; SET @StartRow = 120
DECLARE  @EndRow INT ; 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

Once observation I have is that all Dave Pinal is doing is using a derived table where he is including a ROW_NUMBER() OVER statement which necessitates ordering the table by a set of columns, and we usually order a table when paging anyway. Normally we order the table by the ID field (if it is IDENTITY field or the Date Created field. From this derived-table-with-row-number, we can select only those records where the row number is between two values. Very simple.

In fact this is the same way that LINQ-to-SQL uses to implement skip and take.

The following code is what Dave Pinal gives for paging in SQL Server 2000:


USE AdventureWorks
GO

DECLARE  @StartRow INT ; SET @StartRow = 120
DECLARE  @EndRow INT ;  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

First he creates a temp table #tables which stores all the columns he wants to get out, plus the row number column. (This row number column, RowNumber is an IDENTITY field which is important because it automatically populates itself with the next row number.) Thereafter the entire Person.Contact table (selected columns) is stored in #tables. Finally we select the rows where the RowNumber is between the start and end row numbers. One objection to this code is that we do not need to grab all the data from the Person.Contact table, we should at most grab the TOP 140 records (from the value of @EndRow).

No comments: