The OFFSET-FETCH filter in T-SQL

The OFFSET-FETCH clause is filter using to choose sorted limited data from result set. It is similar to TOP clause, but with extra possibilities. It allow You to specify how many records want skip and how many records return.

The OFFSET and FETCH clause must be use with ORDER BY clause, but the FETCH must be use with OFFSET.
You may not use TOP clause and the OFFSET and FETCH clause in the same query.

Syntax this OFFSET-FETCH  clause is following:

OFFSET { integer | count_expression } { ROW | ROWS }
[ FETCH { FIRST | NEXT } {integer | count_expression } { ROW | ROWS } ONLY ]

After OFFSET may be put integer and ROW or ROWS word.
You may use ROW or ROWS, it is your free choice.
The OFFSET indicates how many rows must be skip to start return rows from query.

After OFFSET clause You may add either FETCH clause with FIRST or NEXT word and integer with count of rows with ROW or ROWS word.  You may use ROW or ROWS, it is your free choice. The FIRST or NEXT word means the same. This clause is ending with ONLY word.
The FETCH clause determines how many rows will be return, but it clause is optional

Begin set AdventureWork2012 database up as current in SQL Server. Select all names of people and modified date from Person table sorted according LastName and FirstName columns.

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName, FirstName;

In result it is 19.972 sorted rows.
11

In the first example we select 10 records skipping 5 rows in result set from AdventureWork2012 database and Person table and Person schema:

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName, FirstName
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

22
In the second example we select 10 records without skipping rows in result set from AdventureWork2012 database and Person table and Person schema. The FETCH clause must use OFFSET so if You would don’t skip any row use zero after OFFSET:

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName, FirstName
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

33

But if You display all records with skip several rows, You may use only OFFSET clause without FETCH clause.

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName, FirstName
OFFSET 3 ROWS;

In result it is 19.969 sorted rows (3 rows was skipped) .
44As I write at the beginning of this topic, the OFFSET-FETCH filter require ORDER BY clause which columns to sort records. How omit it columns and don’t sort? It is way in code below.
If You select all records without ORDER BY and without use OFFSET-FETCH filter, the dispaly result will be as:

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person

66

In result it is 19.972 sorted rows.
Code omitted columns to sort records using OFFSET-FETCH filter:

USE AdventureWorks2012;

SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY (SELECT NULL)
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

55

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.