Friday, October 7, 2011

SQLServer Denali : OFFSET & NEXT Keywords

 

Hi All,

 

I found these two new & very useful keywords in SQLServer Denali

OFFSET & NEXT

 

SELECT *
FROM [Order Details]
ORDER BY [Order ID]
OFFSET 20 ROWS
FETCH NEXT 7 ROWS ONLY

 

 

Above script returns records 21 to 27 from the tables [Order Details]. OFFSET clause will skip the first 20 rows and then returns 7 rows. Please note, this T-SQL will throw error if ORDER BY is not used in the query.

 

Reference: PinalDave's blog

http://blog.sqlauthority.com/2011/10/07/sql-server-server-side-paging-in-sql-server-ce-compact-edition/

 

Namaste!

Anugrah

1 comment:

Anugrah A said...

In case if we are not using SQL SERVER 2012 (Denali) then this can be achieved as given below:

SELECT * FROM (SELECT TOP 27 ROW_NUMBER() OVER (ORDER BY [Order ID]) AS DynamicRowNum, * FROM [Order Details]) AS T WHERE T.DynamicRowNum > 20

I hope you all agree that new keywords supported in SQL Server 2012 are much easy to use :)

Namaste!
Anugrah