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