Paging in sql server

It is best idea to do paging in server side. From Sql Server 2012 we have useful paging mechanism

We can use FETCH and OFFSET to paging in sql server.

FETCH -> indicates number of rows to retrieve

OFFSET-> indicates the number of rows to skip

Syntax for Paging

SELECT <columns>
FROM <tables>
ORDER BY<columns>
OFFSET x
FETCH NEXT y ROWS ONLY

x means number of record to skip and y means number of record to retrieve

Example

SELECT * 
FROM product 
ORDER BY productId 
OFFSET 20 ROWS 
FETCH NEXT 20 ROWS ONLY

In this it skips first 20 records and fetch next 20 rows

But in this some restrictions are there

  • ORDER BY is required
  • TOP is not allowed
Advertisements

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s