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.