Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New and Basic Result-set Paging functionality in SQL 2012 Version.

By Eli Leiba,

This article presents an overview of two clauses which allow paging on results sets when added to the SELECT statement in SQL Server 2012.

When using windows or forms to show the results from a SQL Server database, I used to encounter a problem with fitting a fixed and sorted number of rows into a form designed with a fixed size. In earlier versions of SQL Server, SQL programmers executed queries that retrieved all the data, and afterwards used different search features in order to the locate the row of data they wanted. In SQL Server 2012, new clauses in the SELECT statement were introduced which allow you to retrieve a fixed number of sorted rows that will fit the size of the form shown to the client.

The solution involves using two new clauses to the SELECT statement.

OFFSET ... ROWS 

and

FETCH NEXT.... ROWS ONLY

OFFSET ... ROWS is used to indicate which line number to start with when retrieving results.

FETCH NEXT.... ROWS ONLY is used to indicate how many lines from line number in Expr1 to fetch in order to fit on the page.

An outline of the SELECT statement including these clauses would look like this:

SELECT * FROM...
ORDER BY.....
OFFSET ... ROWS
FETCH NEXT.... ROWS ONLY

Now, suppose you want to show 20 rows from 'Products Table', ordered by the product name, and starting from row number 11 in the results set. Page-up will increase the offset by 20 and Page-down will decrease it by 20 (after checking limits).

The SQL would look like:

SELECT * FROM dbo.Products P
ORDER BY P.productName
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

The OFFSET expression and FETCH NEXT expression are TSQL variables that can be used in place of the constants. If you have SQL Server 2012 installed, check it out for yourself.

Note: This was tested on SQL Server 2012 RC0 version (Sample North wind DB SQL2012 compatible).

Resources:

Ad-Hoc Querying – New Feature in SQL Server 2012 (Code Name DENALI).doc
Total article views: 567 | Views in the last 30 days: 3
 
Related Articles
FORUM

"fetch"

"fetch"

BLOG

Using OFFSET and FETCH

  Introduction Many times while developing our applications we feel the need of pagination, where ...

FORUM

Fetching Remote servers properties

How to Fetch Remote servers properties

BLOG

Offset without OFFSET

A while ago Robert Cary posted an article on SQL Server Central entitled 2005 Paging – The Holy Grai...

FORUM

help me to select ROW_NUMBER in sql server 2000

help me to select ROW_NUMBER in sql server 2000

Tags
sql server 2012    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones