Paging in MS SQL server. (An alternative to Limit in MySQL)

  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