jump to navigation

Using Query Paging in SQL Server 2012 August 7, 2012

Posted by fofo in Sql Server, SQL Server 2012.
Tags: , ,
trackback

In this post I will be looking into a new feature in SQL Server 2012. Actually this is a T-SQL enhancement and it is called Query Paging.Often in our applications we need to page/select a number of rows (or a range of rows if you prefer) by specifying a row count offset in the results. I will be introducing OFFSET which specifies the number of rows to skip  and FETCH which specifies the number of rows after the OFFSET to return.We will use OFFSET/FETCH NEXT in combination with the ORDER BY clause.

I am using the AdventureWorks2012 database.Make sure you visit the CodePlex website and download it.Then attach it (restore it) to your local SQL Server instance.

I will be using SQL Server 2012 Developer Edition but the Express edition will suffice .
I connect to the SQL Server instance.

Type (copy and paste but make sure you review it) the code below.Execute the code below


USE [AdventureWorks2012]

GO

SELECT  [Title] ,
[FirstName] ,
[MiddleName] ,
[LastName] ,
[Suffix]
FROM    [AdventureWorks2012].[Person].[Person]
ORDER BY LastName ,
FirstName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

In this first example I simply instruct SQL Server to skip 0 rows and then fetch me the next 10 rows.

In the same (or in a new query window ) type (copy and paste but make sure you review it) the code below.Execute the code below


SELECT  [Title] ,
[FirstName] ,
[MiddleName] ,
[LastName] ,
[Suffix]
FROM    [AdventureWorks2012].[Person].[Person]
ORDER BY LastName ,
FirstName
OFFSET 500 ROWS
FETCH NEXT 100 ROWS ONLY;

In this second example I simply instruct SQL Server to skip 500 rows and then fetch me the next 100 rows.

I will rewrite the code below to incorporate variables. In this case I simply instruct SQL Server to skip 1000 rows and then fetch me the next 1000 rows.

In the same query window type (copy-paste) but do not execute the code below.


DECLARE @offset INT = 1000;
DECLARE @rows_tofetch INT = 1000;

SELECT  [Title] ,
[FirstName] ,
[MiddleName] ,
[LastName] ,
[Suffix]
FROM    [AdventureWorks2012].[Person].[Person]
ORDER BY LastName ,
FirstName
OFFSET @offset ROWS
FETCH NEXT
@rows_tofetch ROWS ONLY;

In order to get the next page (skip 1000 rows and return 1000 rows after that) we need to rewrite the code above.

Review the code below and then copy and paste it in a new query window.


DECLARE @offset INT = 1000;
DECLARE @rows_tofetch INT = 1000;

SET @offset = 2 * @offset + @rows_tofetch

SELECT  [Title] ,
[FirstName] ,
[MiddleName] ,
[LastName] ,
[Suffix]
FROM    [AdventureWorks2012].[Person].[Person]
ORDER BY LastName ,
FirstName
OFFSET @offset ROWS
FETCH NEXT
@rows_tofetch ROWS ONLY;

Execute the code above.

Hope it helps!!!

Comments»

1. Dot Net Rules : Using Query Paging in SQL Server 2012 - August 7, 2012

[…] after the OFFSET to return.We will use OFFSET/FETCH NEXT in combination with the ORDER BY clause. (read more) Share Posted: Τρίτη, 7 Αυγούστου 2012 3:09 πμ από το μέλος […]


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

%d bloggers like this: