SQL Server Denali has a new feature allowing pagination using the order by clause.
A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the old fashioned database paging solution.
This paging solution initially grabs a subset of a table and counts the records. It then stores ordered results based on the parameter passed into the common table expression. Additional parameters are the number of rows the caller wants on each page and the page number the caller is currently retrieving.
@SortColumn VARCHAR(50) = NULL,
@iRows INT = 10,
@iPageNum INT = 1
SET NOCOUNT ON
DECLARE @RecordCount INT
DECLARE @iNbrPages INT
SET @RecordCount = 0
SET @iNbrPages = 0
FROM HR.Employees emp
WHERE emp.IsTerminated = 1
@iNbrPages = CEILING(COUNT(1)/(@iRows*1.0)),
@RecordCount = COUNT(1)
CASE WHEN @SortColumn = 'EmployeeID' THEN emp.EmployeeID END ASC,
CASE WHEN @SortColumn = 'FirstName' THEN emp.FirstName END ASC,
CASE WHEN @SortColumn = 'LastName' THEN emp.LastName END ASC,
CASE WHEN @SortColumn = 'DateHired' THEN emp.DateHired END ASC
) AS [Row_ID],
FROM #Employees emp
PageNumber = @iPageNum,
TotalNbrPages = @iNbrPages,
TotalRecords = @RecordCount
FROM PagingCTE emp
WHERE Row_ID <= (@iRows * @iPageNum) - (@iRows - 1)
AND Row_ID <= @iRows * @iPageNum
A more efficient way to do this would be to pass in a sessionID unique to the user, then create a temp table based upon that users sessionID. Then on each call to the procedure, the procedure could check for the existence of the temp table so the result would not have to be queried again.