Blog Post

OFFSET and FETCH Query Options in SQL Server 2012

,

If you are tired of implementing query paging solution in old classic style than try query hints OFFSET & FETCH newly introduced in SQL Server 2012. You might have used TOP operator to return the top number of rows from a table. Probably you might also have used TOP operator to answer your complex query need for pagination. OFFSET & FETCH query clauses can give you more advantages over the TOP operator. OFFSET & FETCH query clauses also make your life easier when you want to avoid row by row operation such like cursor.


Let us say you have 150,000 records in a table and you want to query 100 rows starting from 125000. In this case you can use OFFSET and FETCH to avoid cursor or PARTITION BY clause. OFFSET and FETCH can be used together to get the exact set of data you are looking for.


OFFSET: allows you to offset the record pointer to a specific row in a table


FETCH: allows you to fetch/return number of rows you request in Fetch.


Let’s take an example of querying 5 rows from a table that has 10 records. To make this an interesting example, lets query 5 records from a 10 records table starting from the 4th record: 


CREATE TABLE STUDENT

CREATE TABLE STUDENT

(

Id INT IDENTITY(1,1),

Name VARCHAR(20)

)

GO

INSERT ROWS INTO STUDENT TABLE

INSERT INTO STUDENT VALUES ('Manish');

INSERT INTO STUDENT VALUES ('Kumar');

INSERT INTO STUDENT VALUES ('John');

INSERT INTO STUDENT VALUES ('Smith');

INSERT INTO STUDENT VALUES ('Vikas');

INSERT INTO STUDENT VALUES ('Sandeep');

INSERT INTO STUDENT VALUES ('Gautam');

INSERT INTO STUDENT VALUES ('Prasad');

INSERT INTO STUDENT VALUES ('Dinesh');

INSERT INTO STUDENT VALUES ('Nidhi');

GO;

QUERYING THE TABLE USING OFFSET AND FETCH

--In below query, we are offsetting the rows by 3 records (starting at 4th record) and returning the next 5 rows.

SELECT * FROM STUDENT

ORDER BY Id

OFFSET 3 ROWS

FETCH NEXT 5 ROWS ONLY

GO;

--You will get below result

Id    Name

4     Smith

5     Vikas

6     Sandeep

7     Gautam

8     Prasad

(5 row(s) affected) 

--you can also use variables with offset and fetch clauses.

DECLARE @offset INT=3, @fetch INT=5

SELECT * FROM STUDENT

ORDER BY Id

OFFSET @offset ROWS

FETCH NEXT @fetch ROWS ONLY

GO;

Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating

Blog Post

OFFSET and FETCH Query Options in SQL Server 2012

,

If you are tired of implementing query paging solution in old classic style than try query hints OFFSET & FETCH newly introduced in SQL Server 2012. You might have used TOP operator to return the top number of rows from a table. Probably you might also have used TOP operator to answer your complex query need for pagination. OFFSET & FETCH query clauses can give you more advantages over the TOP operator. OFFSET & FETCH query clauses also make your life easier when you want to avoid row by row operation such like cursor.


Let us say you have 150,000 records in a table and you want to query 100 rows starting from 125000. In this case you can use OFFSET and FETCH to avoid cursor or PARTITION BY clause. OFFSET and FETCH can be used together to get the exact set of data you are looking for.


OFFSET: allows you to offset the record pointer to a specific row in a table


FETCH: allows you to fetch/return number of rows you request in Fetch.


Let’s take an example of querying 5 rows from a table that has 10 records. To make this an interesting example, lets query 5 records from a 10 records table starting from the 4th record: 


CREATE TABLE STUDENT

CREATE TABLE STUDENT

(

Id INT IDENTITY(1,1),

Name VARCHAR(20)

)

GO

INSERT ROWS INTO STUDENT TABLE

INSERT INTO STUDENT VALUES ('Manish');

INSERT INTO STUDENT VALUES ('Kumar');

INSERT INTO STUDENT VALUES ('John');

INSERT INTO STUDENT VALUES ('Smith');

INSERT INTO STUDENT VALUES ('Vikas');

INSERT INTO STUDENT VALUES ('Sandeep');

INSERT INTO STUDENT VALUES ('Gautam');

INSERT INTO STUDENT VALUES ('Prasad');

INSERT INTO STUDENT VALUES ('Dinesh');

INSERT INTO STUDENT VALUES ('Nidhi');

GO;

QUERYING THE TABLE USING OFFSET AND FETCH

--In below query, we are offsetting the rows by 3 records (starting at 4th record) and returning the next 5 rows.

SELECT * FROM STUDENT

ORDER BY Id

OFFSET 3 ROWS

FETCH NEXT 5 ROWS ONLY

GO;

--You will get below result

Id    Name

4     Smith

5     Vikas

6     Sandeep

7     Gautam

8     Prasad

(5 row(s) affected) 

--you can also use variables with offset and fetch clauses.

DECLARE @offset INT=3, @fetch INT=5

SELECT * FROM STUDENT

ORDER BY Id

OFFSET @offset ROWS

FETCH NEXT @fetch ROWS ONLY

GO;

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating