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




Related Articles :

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 |