How to use OFFSET / FETCH option in SQL Server Order By Clause ?

Leave a comment (1) Go to comments

What is OFFSET FETCH  options in SQL Server ?

SQL Server Denali (2011) has enhanced the ORDER BY clause. Now with ORDER BY clause we can also specify the OFFSET and FETCH options. By using OFFSET and FETCH I can specify, I wanted to have 6 records in  result sets and those 6 records should start from 40th record. OFFSET/FETCH are designed  mainly for ad-hoc paging purpose.

Lets understand this quickly with an Example with a Sample Table, for Demonstration

-- Creating a Sample Table which has only one column as ID
create table paging_test
(
ID int
)
go
--- Inserting 100 records in that table in one go using While Loop
declare @i int = 1
while (@i<100)
begin
    insert    paging_test values (@i)
    set @i += 1
end

How to use OFFSET / FETCH  option in SQL Server Order By Clause ? What is OFFSET FETCH in SQL Server SQL Server order by enhancement sql server denali fetch offset demo sql server 2011 order by enhancement example sql server 2011 order by demo sql server 2011 offset fetch example order by enhancement example OFFSET FETCH options in ORDER by


Example: SELECT QUERY using ORDER BY clause including OFFSET & FETCH

--- Seleting 6 rows starting from 40th record
select * from paging_test
order by ID
Offset 40 rows
fetch next 6 rows only

OUTPUT

How to use OFFSET / FETCH  option in SQL Server Order By Clause ? What is OFFSET FETCH in SQL Server SQL Server order by enhancement sql server denali fetch offset demo sql server 2011 order by enhancement example sql server 2011 order by demo sql server 2011 offset fetch example order by enhancement example OFFSET FETCH options in ORDER by

In the above query, we mentioned we wanted to select records from (40) and we wanted to select only 6 (six) record from that start point.

OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one. Taken from Books Online of Denali

NOTES

  • Pagination for programmers is now very easy, previously we need to use ROW_NUMBER(), TOP, Order BY clauses to get similar output
  • Using OFFSET and FETCH is bit faster then using ROW_NUMBER(), TOP, Order BY clauses
  • Very Simple to use for Ad-Hoc paging
  • ROW and ROWS are synonyms and are provided for ANSI compatibility.
  • FETCH can be used with either FIRST or NEXT like
    • FETCH  NEXT 6 ROWS  ONLY or FETCH FIRST 6 ROWS ONLY anything can be used
    • OFFSET (40) ROWS or OFFSET (40) ROW or Offset 40 rows anything can be used

Tags : What is OFFSET FETCH  in SQL Server,OFFSET FETCH  options in ORDER by, SQL Server order by enhancement,order by enhancement example, sql server 2011 order by demo,sql server 2011 offset fetch example, sql server denali fetch offset demo

EOF - How to use OFFSET / FETCH option in SQL Server Order By Clause ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: