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.
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;
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;
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) .
As 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
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;