PhuocLe - Microsoft Dynamics CRM

Paging in various versions of SQL Server Oct/31/2013


1. SQL CE

SELECT  *
FROM    graffiti_Posts p
ORDER BY p.Id ASC 
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

2. SQL Server 2000

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    ( SELECT TOP ( @RowsPerPage )
                    *
          FROM      ( SELECT TOP ( ( @PageNumber ) * @RowsPerPage )
                                *
                      FROM      dbo.graffiti_Posts
                      ORDER BY Id
                    ) AS posts1
          ORDER BY  Id DESC
        ) AS posts2
ORDER BY Id ASC

3. SQL Server 2008/R2, SQL Server 2005

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY Id ) AS RowNum
          FROM      graffiti_Posts
        ) AS post
WHERE   post.RowNum BETWEEN ( ( @PageNumber - 1 ) * @RowsPerPage ) + 1 AND @RowsPerPage * ( @PageNumber )

4. SQL Server 2012

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    graffiti_Posts
ORDER BY Id
OFFSET ( @PageNumber - 1 ) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

 


Category: SQL
Tags:

Enter your comment

Your Name (*)
Your Email (*)
Website (*)