Paging data with SQL Server 2005
The project I am working on required sorting, filtering and paging of large data sets. This isn't an uncommon requirement; yet the solutions are often either fiddly or not very performant. But fortunately, using SQL Server 2005's common table expressions and the new ROW_NUMBER() function, I managed to create a stored procedure that provides sorting, filtering and paging of large volumes of data for any table - and surprisingly it's really fast too! The code is at the end of the post. Feel free to try it out.
The most obvious advantage of the common table expression approach is that it results in much simpler code. You don't end up working with temporary tables or table variables like you had to in SQL Server 2000. As Nigel Rivett explains ,
"A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on."
In addition to the terseness of CTEs, I imagine that SQL Server is probably super-optimised to work with them. I haven't done any benchmark tests to prove that a CTE approach is more performant than a temp table approach - but I'd be willing to wager that it is. I'll leave the comparison as an excercise for the reader (or for me if I find the time).
Anyway, the proc returns two result sets: the first contains the page of data; the second contains a single value, which is the total number of records that would have been returned without paging. By dividing the total number of records by the page size, you get the total number of pages, which allows you to display things like "Page 3 of 3904".
I thought that the dynamic SQL would make it much slower, but the impact was imperceptible even with large data sets. SQL Server 2005 continues to impress me.
I hope someone finds this stored proc useful.
-
/****** Object: StoredProcedure [dbo].[GetDataPage] Script Date: 08/12/2006 23:44:34 ******/
-
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDataPage]') AND type IN (N'P', N'PC'))
-
DROP PROCEDURE [dbo].[GetDataPage]
-
SET ANSI_NULLS ON
-
SET QUOTED_IDENTIFIER ON
-
go
-
-
-
-- =============================================
-
-- Author: Stuart Campbell
-
-- Create date: 25 July 2006
-
-- Description: Returns a page of data from a table. The data can optionally be filtered and sorted.
-
-- =============================================
-
CREATE PROCEDURE [dbo].[GetDataPage]
-
@tableName sysname,
-
@primaryKey nvarchar(100),
-
@startRowIndex int = NULL,
-
@maximumRows int = NULL,
-
@SortColumn nvarchar(100) = NULL,
-
@SortDirection char(4) = 'ASC', -- ASC or DESC
-
@Filter nvarchar(1000) = NULL
-
AS
-
BEGIN
-
SET NOCOUNT ON;
-
-
DECLARE @endRowIndex int
-
SELECT @endRowIndex = @startRowIndex+@maximumRows-1
-
-
-
DECLARE @FROM varchar(1000)
-
DECLARE @orderBy varchar(100)
-
-
SELECT @FROM = ' FROM ' + @tableName;
-
IF (@filter IS NOT NULL) AND (@filter <> '')
-
SELECT @FROM = @FROM + ' WHERE ' + @filter
-
-
IF (@sortColumn IS NOT NULL) AND (@sortColumn <> '')
-
SET @orderBy = ' ORDER BY ' + @sortColumn + ' ' + COALESCE(@sortDirection, '')
-
ELSE
-
SET @orderBy = ' ORDER BY '+ @primaryKey;
-
-
EXEC('WITH cte AS (SELECT ROW_NUMBER() OVER (' + @orderBy + ') AS RowNumber, * ' + @FROM + ')
-
SELECT *
-
FROM cte
-
WHERE RowNumber between '+ @startRowIndex + ' AND ' + @endRowIndex);
-
-
EXEC('SELECT COUNT(1) as TotalRows ' + @FROM);
-
-
SET NOCOUNT ON
-
END