How to Return Results within a Specified Range

Jonathan Kehayias, April 15, 2008

When working with a application on a large database, some result sets maybe
to large to efficiently return them to the application. The .NET gridview controls
are commonly used for displaying tabular data to the end user. To provide
more effective use of the data, it is often paged so that only 10 rows display at
a time. To make this as efficient as possible, you can use a stored procedure
that only returns the necessary range back to the application.

There are several methods to accomplish a Range Based Output. This demonstration
will provide a Solution for SQL Server 2005 / SQL Server 2008 using the ROW_NUMBER()
function, as well as a T-SQL Solution for SQL Server 2000 using a table variable.

SQL Server 2005 / SQL Server 2008 Solutions

Derived Table

--Query to Retrieve Desired Data
DECLARE @Start datetime,
        @End datetime
 
SELECT  @Start = 11,
        @End = 20
 
SELECT  objName, CrDate
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY name)
             AS Row, convert(varchar(30), name) as objName, crDate FROM sysobjects)
            AS DatabaseObjects
WHERE  Row >= @Start AND Row <= @End
 
/*  Results
objName                        CrDate
------------------------------ -----------------------
assembly_modules               2007-02-10 00:23:25.353
assembly_references            2007-02-10 00:23:29.183
assembly_types                 2007-02-10 00:23:27.870
asymmetric_keys                2007-02-10 00:23:52.917
backup_devices                 2007-02-10 00:23:41.870
certificates                   2007-02-10 00:23:52.260
check_constraints              2007-02-10 00:23:20.760
CHECK_CONSTRAINTS              2007-02-10 00:27:23.027
COLUMN_DOMAIN_USAGE            2007-02-10 00:27:21.060
COLUMN_PRIVILEGES              2007-02-10 00:27:21.387
*/
 

Return to Top

Common Table Expression

--Query to Retrieve Desired Data
DECLARE         @Start datetime,
        @End datetime
 
SELECT  @Start = 11,
        @End = 20
 
;WITH ObjectsTABLE AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY name) AS Row, 
                convert(varchar(30), name) as objName,
                crDate
 FROM sysobjects
)
 
SELECT  objName, CrDate
FROM ObjectsTable
WHERE  Row >= @Start AND Row <= @End
 
/*  Results
objName                        CrDate
------------------------------ -----------------------
assembly_modules               2007-02-10 00:23:25.353
assembly_references            2007-02-10 00:23:29.183
assembly_types                 2007-02-10 00:23:27.870
asymmetric_keys                2007-02-10 00:23:52.917
backup_devices                 2007-02-10 00:23:41.870
certificates                   2007-02-10 00:23:52.260
check_constraints              2007-02-10 00:23:20.760
CHECK_CONSTRAINTS              2007-02-10 00:27:23.027
COLUMN_DOMAIN_USAGE            2007-02-10 00:27:21.060
COLUMN_PRIVILEGES              2007-02-10 00:27:21.387
*/

Return to Top

SQL 2000 Solutions

--Query to Retrieve Desired Data
DECLARE         @Start datetime,
        @End datetime
 
SELECT  @Start = 11,
        @End = 20
 
DECLARE @PagingTable TABLE
(Row int identity primary key,
 objName varchar(30),
 crDate datetime)
 
INSERT INTO @PagingTable (objName, crDate)
SELECT  convert(varchar(30), name) as objName,
                crDate
FROM sysobjects
ORDER BY Name
 
SELECT  objName, CrDate
FROM @PagingTable
WHERE  Row >= @Start AND Row <= @End
 
/*  Results
objName                        CrDate
------------------------------ -----------------------
assembly_modules               2007-02-10 00:23:25.353
assembly_references            2007-02-10 00:23:29.183
assembly_types                 2007-02-10 00:23:27.870
asymmetric_keys                2007-02-10 00:23:52.917
backup_devices                 2007-02-10 00:23:41.870
certificates                   2007-02-10 00:23:52.260
check_constraints              2007-02-10 00:23:20.760
CHECK_CONSTRAINTS              2007-02-10 00:27:23.027
COLUMN_DOMAIN_USAGE            2007-02-10 00:27:21.060
COLUMN_PRIVILEGES              2007-02-10 00:27:21.387
*/
 

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, April 15, 2008