Custom Paging In SQL Server 2012 (Denali)


SQL Server 2012 solved big problem in custom paging with easy way. lets start.

The paging now can be done using ORDER clause.

  • Return all rows sorted by FirstName
SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC
  • Skip the top 10 rows and return all the next rows, OFFSET <int> ROWS (NEW)
SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC
  OFFSET 10 ROWS
  • Paging (NEW)
DECLARE @PageSize int;
SET @PageSize=10;

DECLARE @CurrentPage int;
SET @CurrentPage=0;

SELECT[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]

  FROM [AdventureWorks2012].[Person].[Person]
  ORDER BY FirstName ASC

  OFFSET (@PageSize * @CurrentPage) ROWS FETCH NEXT @PageSize ROWS ONLY 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s