Server Side Paging simplified in SQL Server “Denali”


SQL Server “Denali” is the new upcoming version of SQL Server, it has numerous new features

Let us review the new built-in Paging feature “FETCH FIRST”, “FETCH NEXT”

–How to Fetch 1st Page of 10 Rows

USE [AdventureWorks2008R2]
GO

DECLARE @PageNumber INT = 1
DECLARE @PageSize INT = 10

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH FIRST @PageSize ROWS ONLY

–How to Fetch 2nd Page of 10 Rows

SET @PageNumber = 2

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET (@PageNumber * @PageSize) – @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

— How to Fetch all records skipping first 100 Records

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 100 ROWs

— How to implement Customizable Paging support using the new feature

–Create a Personalization table to store PageSettings

CREATE TABLE PageSettings
(
    LoggedInUserName    VARCHAR(128),
    PageSize        Int
)

–Add some Page setting Records

INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘bill’,10)
INSERT INTO PageSettings (LoggedInUserName, PageSize) Values (‘gates’,20)

— Create a Sample Stored Procedure to implement customized paging

CREATE PROCEDURE sp_FetchProducts
(
    @LoginName VARCHAR(128)
)
As
BEGIN

SELECT [ProductID],[Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[Color],[StandardCost],[ListPrice]
  FROM [Production].[Product]
  ORDER BY [Name]
  OFFSET 10 ROWS
  FETCH NEXT (SELECT PageSize From PageSettings WHERE LoggedInUserName=@LoginName) ROWS ONLY 

END

–Execute the Stored Procedure under login “Bill”

EXEC sp_FetchProducts @LoginName = ‘bill’

Output: Stored Procedure returns 10 Records

image

–Execute the Stored Procedure under login “Gates”

EXEC sp_FetchProducts @LoginName = ‘gates’

Output: Stored Procedure now returns 20 Records

image

You can even customize this solution further Table wise as well.

I hope all the SQL developers will enjoy this feature, since it reduces lot of coding for implementing paging using Table Variables or Temp Table or CTEs.

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s