New T-SQL Features in SQL Server 2012

OFFSET and
FETCH

SQL Server Denali has introduced a new
feature to make paging more efficient.

Legacy Method

In previous versions when we needed to code the
paging of results or for example, get the second highest salary from a payroll
table, we need to write a complex code having NOT IN which is a low performance
code.

2011 Method

SQL Server 2011 introduces the  OFFSET
command for paging or selecting for example the highest salary from  a table.

The OFFSET with FETCH commands can limit the
number of rows sent to the client. Instead of using the TOP command for paging,
these new commands can be used to reduce complexity. These new commands will
normally be accompanied with ORDER BY.

For example:

–Leave first 10 rows and Fetch next 5
rows

SELECT ProductID, Name   

FROM AdventureWorks.Production.Product

ORDER BY NAME

OFFSET 10 ROWS

FETCH NEXT 5 ROWS ONLY

OFFSET in the above query will hide the first
10 rows and FETCH NEXT will show next 5 rows, which are ordered on Name.

Let’s look at how  OFFSET can be helpful in
Paging, by comparing both methods. In this example, we need to select the third
page with each page having 5 records.

This can be achieved by a combination of
TOP, ORDER and NOT IN.

–Legacy method

SELECT TOP(5) ProductID, Name

FROM AdventureWorks.Production.Product

WHERE ProductID NOT IN(SELECT TOP(10) ProductID FROM 
AdventureWorks.Production.Product ORDER BY NAME)

ORDER BY NAME

Using OFFSET and FETCH NEXT it is easy to
achieve the above result:

–2011 method

SELECT ProductID, Name

FROM AdventureWorks.Production.Product

ORDER BY NAME

OFFSET 10 ROWS

FETCH NEXT 5 ROWS ONLY

 

Continues…

Pages: 1 2 3 4




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |