Paging in SQL Server

Hello readers, In this tutorial, I am going to show you how to achieve paging in SQL Server 2012. SQL Server introduced various features to get records in pages.

Paging in SQL Server:

A big challenge for any developer is to deal with large number of records. A best way to deal with large number of records is paging. SQL Server provides many features to get records in pages. Here i am showing you how can we achieve paging in SQL Server.

Create Database:

CREATE DATABASE PagingDemo

Use Database:

USE PagingDemo

Create Table:

CREATE TABLE [dbo].[Employee](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [Gender] [varchar](10) NULL,
 [Address] [varchar](150) NULL,
 [Email] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Insert Records:

SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (1, N'Rohit', N'Singh', N'Male', N'Varanasi', N'rohit@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (2, N'Vivek', N'Tripathi', N'Male', N'New Delhi', N'vivek@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (3, N'Raxmi', N'Singh', N'Femaile', N'Lucknow', N'raxmi@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (4, N'Akash', N'Sinha', N'Male', N'Noida', N'akash@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (5, N'Rohan', N'Dixit', N'Male', N'Pune', N'rohan@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (6, N'Ketan', N'Mehta', N'Male', N'Mumbai', N'ketan@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (7, N'Vikas', N'Singh', N'Male', N'Kolkata', N'vikas@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (8, N'Nikhil', N'Pandey', N'Male', N'New Delhi', N'nikhil@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (9, N'Arvind', N'Singh', N'Male', N'Mumbai', N'arvind@testmail.com')
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [Gender], [Address], [Email]) VALUES (10, N'Karan', N'Mehta', N'Male', N'Varanasi', N'karan@testmail.com')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Query for Paging:

DECLARE @RowsPerPage INT = 5, @PageNumber INT = 1


SELECT [ID]
 ,[FirstName]
 ,[LastName]
 ,[Gender]
 ,[Address]
 ,[Email]
 FROM [dbo].[Employee]
 ORDER BY [ID]
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO

You can see we are fetching 5 records per page. Run the above query.

View More:

• Check Constraint in SQL Server.
• Primary Key in SQL Server.
• System Databases in SQL Server.
• Generate Password using Stored Procedure in SQL Server.

Conclusion:

I hope you understand this this important tutorial about Paging in SQL Server. Your Feedback, Comments are always welcome to me.

Thank You.