How to select 12 rows of a given result set for use in DataGrid/Paging? Need to override Grid default caching.

Question

I have a SQL query that will return over 10,000 rows. Since the client only will view 12 rows at a time, how do I alter my SQL so that I can select just the needed rows?

My expectation is that I'll requery the database each time the user clicks on the grid. My solution is based on the demo below, however I'm trying to make this work with the "OrderDetail" table of northwind which doesn't have a clean/sequential ID value.

http://demos.telerik.com/aspnet-ajax/grid/examples/client/virtualscrollpaging/defaultcs.aspx

I'm using SQL Express in my local dev environment, but will use SQL 2008 in Production.

Answer 1

WITH [EMPLOYEE ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
SELECT FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID <= 12

Answer 2

You can use the ROW_NUMBER() function:

WITH paging AS 
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY COLUMN) AS rowNum,
        ...
    FROM table
) 
SELECT *
FROM paging
WHERE rowNum BETWEEN 1 AND 12

This creates a CTE, but could use a temp table or table variable as well. You could then add some parameters to specify the integers for the BETWEEN clause.