Basic Fetch and Offset Experiments–#SQLNewBlogger

I’ve never used the FETCH or OFFSET commands for pagination, but I have heard of them. I ran across them recently and decided to experiment a bit.

One note: I have seen notes about performance, so before you do more than experiment, read about the issues (SQLPerformance, Use the Index, Luke)

This is part of the ORDER BY clause, and this allows you to skip a number of rows and then also only get a certain number of rows in the result set. The basic syntax is:

… ORDER BY XX
OFFSET YYY ROWS
FETCH {FIRST|NEXT} ZZZ ROWS

This means, if I have a query link this one, I get the first ten rows with a 0 offset.

SELECT 
  f.FlightDate, f.DepartureAirport, f.DestinationAirport
  FROM dbo.Flight AS f
  ORDER BY f.FlightDate
  OFFSET 0 ROWS
  FETCH FIRST 10 ROWS ONLY

If I want the next 10, I can change the offset to 10.

SELECT 
  f.FlightDate, f.DepartureAirport, f.DestinationAirport
  FROM dbo.Flight AS f
  ORDER BY f.FlightDate
  OFFSET 10 ROWS
  FETCH NEXT 10 ROWS ONLY

The OFFSET must proceed the FETCH, and OFFSET can be 0. If I want to make this page, I need to ensure I change the value for OFFSET to skip the rows already returned. I can use variables here:

DECLARE @offset INT = 2
, @fetch INT = 4;

SELECT 
  f.FlightDate, f.DepartureAirport, f.DestinationAirport
  FROM dbo.Flight AS f
  ORDER BY f.FlightDate
  OFFSET @offset ROWS
  FETCH FIRST @fetch ROWS ONLY

This gets me the 3rd through 6th rows in my dataset. I’ve included a vertical partition here to let me test without having to remember which rows are which.

2021-03-22 14_36_31-SQLQuery1.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (57))_ - Microsoft

This is a really basic look at the native way for paging through data, though beware the entire query runs and then the engine filters out data. This may or not be a big performance issues, but on large amounts of data it will be.

SQLNewBlogger

A quick look at a feature I ran across. I needed to test code for someone and verify it works, which means I needed to take 10 minutes and try a few queries. This entire post took my about 15 minutes to write and it gives me ideas for other posts.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.