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.
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 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 the method of SQL paginataion with this new enhancement
of Order By clause .