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; |
