If you are a .NET developer , then you might know the paging capabilities of ASP.NET data
controls like Gridview. But the main problem associated with this paging is that , each time a
new page is requested all the records are transferred from the data server. If the data server
and your application is in different physical locations, then it causes a performance dropdown.
So it will be always better if we can do the paging in SQL server. In MySQL this is
straight forward and needs no complex queries.
SELECT * FROM tablename ORDER BY key LIMIT 10 OFFSET 100 .. simple.
But in MS SQL the keyword Limits or its alternative is not available.
So we need to find our own solution for this.
LIMIT in MSSQL - solution using ROW_NUMBER()
ROW_NUMBER() Returns the sequential number of a row in a result set, starting at 1 for the first
row.
Let's consider an example.
Suppose you have a table.
CREATE TABLE [dbo].[JobDetails](
[ControlID] [int] NOT NULL,
[JobDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priority] [int] NULL,
[CreatedOn] [datetime] NULL,
CONSTRAINT [PK_JobDetails] PRIMARY KEY CLUSTERED
(
[ControlID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And records..
ALTER TABLE dbo.JobDetails NOCHECK CONSTRAINT ALL
-- Insert new records
INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (1,
'2008-10-10 00:00:00', 'Job Description 1', 10)
INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (2,
'2008-11-10 00:00:00', 'Job Description 2', 10)
INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (3,
'2008-10-10 00:00:00', 'Job Description 3', 11)
INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (4,
'2008-10-10 00:00:00', 'Job Description 4', 12)
INSERT INTO dbo.JobDetails ([ControlID], [CreatedOn], [JobDescription], [Priority]) VALUES (5,
'2008-10-10 00:00:00', 'Job Description 5', 9)
-- Enable all check on table
ALTER TABLE dbo.JobDetails CHECK CONSTRAINT ALL
Now , I need to include paging for this records. I have to get the job order by Priority and
CreatedOn Date. So here is the code to do that.
DECLARE @PageNumber int
SET @PageNumber = 1
DECLARE @PageSize int
SET @PageSize = 3
SELECT *
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Priority asc , CreatedOn desc ) AS
sequenceNum,
*
FROM JobDetails
-- WHERE ...(you can include where condition here if u need)
)temp
WHERE temp.sequenceNum BETWEEN ((@PageNumber-1)*@PageSize)+1 AND (@PageNumber *
@PageSize)
Hope this will be useful for you. If you want to see a live implementation of this query, you
can see the following page in a job site. Click here to view
demo