SQL Server – OFFSET FETCH Clause

The OFFSET-FETCH option is extremely useful for paging result sets.

Main features:

  • Introduced in SQL Server 2012.
  • It appears after the ORDER BY clause which is mandatory to use OFFSET/FETCH.
  • The OFFSET clause indicates how many rows to skip and the FETCH clause indicates how many rows to return after skipping.
  • The FETCH clause always requires an OFFSET clause. So if you don´t want to skip any rows, you need to specify: OFFSET 0 ROWS.

EXAMPLE

DECLARE @Customers table (
custid int IDENTITY(1,1),
companyname varchar(30),
contactname varchar(30));

INSERT INTO @Customers (companyname,contactname)
VALUES
('Customer MLTDN','Hassall, Mark'),
('Customer KBUDE','Peoples, John'),
('Customer HFBZG','Arndt, Torsten'),
('Customer HGVLZ','Higginbotham, Tom'),
('Customer XHXJV','Poland, Carole'),
('Customer QXVLA','Bansal, Dushyant'),
('Customer QUHWH','Ilyina, Julia'),
('Customer RTXGC','Raghav, Amritansh'),
('Customer EEALV','Bassols, Pilar Colome'),
('Customer UBHAU','Jaffe, David');

SELECTING ALL ROWS FROM @Customers

SELECT *
FROM   @Customers
ORDER  BY custid;

Selecting all rows from @Customers

USING OFFSET

SELECT *
FROM @Customers
ORDER BY custid
OFFSET 2 ROWS;

OFFSET_FETCH_02

skip first two rows

USING OFFSET WITH FETCH

SELECT *
FROM @Customers
ORDER BY custid
OFFSET 2 ROWS
FETCH NEXT 5 ROWS ONLY;

OFFSET_FETCH_03

Skip first two rows and return next five rows.

USING ONLY FETCH (ERROR)

SELECT *
FROM @Customers
ORDER BY custid
FETCH NEXT 5 ROWS ONLY;

OFFSET_FETCH_04

OFFSET is mandatory.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s