In earlier version of SQL server, when we need to SQL pagination then we were using temp table, Common Table expression or Ranking functions.
One best feature introduced by SQL Server 2011 and that enhancement is with ORDER BY clause.
Yes this is the feature introduced by SQL Server Denali and we can use this option to get the pagination.
We can specify the Order by clause with OFFSET and FETCH NEXT keywords. Definition looks as following.
OFFSET : Determine the start row to begin on.
FETCH NEXT : How many rows need to fetch.
Let's look on some examples of ORDER BY clause with OFFSET and FETCH NEXT.
Creating Database and table objects.
Creating Sequence
Inserting records using sequence and looks for inserted records.
Let us fetch first 10 records from table using OFFSET and FETCH NEXT
I hope you already started this new enhancement of Order By clause to implement pagination.
One best feature introduced by SQL Server 2011 and that enhancement is with ORDER BY clause.
Yes this is the feature introduced by SQL Server Denali and we can use this option to get the pagination.
We can specify the Order by clause with OFFSET and FETCH NEXT keywords. Definition looks as following.
OFFSET : Determine the start row to begin on.
FETCH NEXT : How many rows need to fetch.
Let's look on some examples of ORDER BY clause with OFFSET and FETCH NEXT.
Creating Database and table objects.
CREATE DATABASE PaginationDB GO use PaginationDB GO IF (OBJECT_ID('PaginationTable') > 0) DROP TABLE PaginationTable GO CREATE table PaginationTable ( PageId INT, PageName varchar(100) ) GO
Creating Sequence
IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence') DROP SEQUENCE NewSequence; GO CREATE SEQUENCE [NewSequence] AS INT START with 1 INCREMENT by 1 MAXVALUE 1000 GO
Inserting records using sequence and looks for inserted records.
INSERT INTO PaginationTable SELECT next value for [NewSequence], name FROM sys.objects GO SELECT PageId,PageName FROM PaginationTable GO
Let us fetch first 10 records from table using OFFSET and FETCH NEXT
-- Fetch first 10 rows DECLARE @PageIndex INT = 1, @Pagesize INT = 10 SELECT PageId, PageName FROM PaginationTable ORDER BY PageId OFFSET (@PageIndex - 1) * @Pagesize ROWS FETCH NEXT @Pagesize ROWS ONLY GO
-- Fetch next 10 rows DECLARE @PageIndex INT = 2, @Pagesize INT = 10 SELECT PageId, PageName FROM PaginationTable ORDER BY PageId OFFSET (@PageIndex - 1) * @Pagesize ROWS FETCH NEXT @Pagesize ROWS ONLY GO
-- Fetch Last rows DECLARE @PageIndex INT = 1, @Pagesize INT = 10 DECLARE @TotalRows INT = (SELECT COUNT(1) FROM PaginationTable ) SELECT PageId, PageName FROM PaginationTable ORDER BY PageId OFFSET (@TotalRows / @Pagesize ) * @Pagesize ROWS FETCH NEXT @Pagesize ROWS ONLY GO
I hope you already started this new enhancement of Order By clause to implement pagination.
Nice article paresh, good job, keep it up.
ReplyDeleteThank you,
Fazal
Good One. Thanks for sharing this knowledge
ReplyDelete