Pagenation SQL Server solution

1. It's easy in SQL 2012. it has similar feature as those in MySQL and PostGreSQL. Use OFFSET and FETCH to limit the rows it returns back.

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

2. In SQL 2005 or 2008, ranking function row_num() can be used. Drawback is the the performance overhead. CTE can be used here to make it look better.

SELECT  *

FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *

          FROM      Orders

          WHERE     OrderDate >= '1980-01-01'

        ) AS RowConstrainedResult

WHERE   RowNum >= 1

    AND RowNum < 20

ORDER BY RowNum

WITH Paging_CTE AS

(

SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *

          FROM      Orders

          WHERE     OrderDate >= '1980-01-01'

)

SELECT

*

FROM

Paging_CTE

WHERE RowNum > 0 AND RowNum <= 20

3. as more common approach, in SQL 2000, due to lack of features, some other way need to be invented.

3.1 use temporary table

CREATE TABLE #Temp (

    ID int IDENTITY PRIMARY KEY,

    PK  /* this can be combined PK */

)

INSERT INTO #Temp SELECT PK FROM Table where xxxx ORDER BY SortColumns

SELECT xxx FROM Table JOIN #Temp temp ON Table.PKs = temp.PKs 

WHERE ID > StartRow AND ID < EndRow

ORDER BY temp.ID 

3.2 set rowcount with proper sort column, very inefficient

SET ROWCOUNT @StartRow

SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn

SET ROWCOUNT @PageSize

SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

3.3 retrieve all the rows and put in web session. pagenation is done by web app.