Basic OFFSET–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The other day I saw an article on the OFFSET clause in a SELECT. I had seen this come out and looked at it briefly in SQL Server 2012, but hadn’t done much with it.

NOTE: if you use this, be sure you read about potential performance problems and solutions.

The basic structure of this clause is that it is a part of the ORDER BY section of a query. After the column ordering, I can enter OFFSET and a value, which will skip those rows. I can optionally enter a number of rows to fetch.

The structure is:

<query>
ORDER BY col1, col2
OFFSET n ROWS FETCH NEXT 10 ROWS ONLY

This code:

WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT *
FROM myTally
ORDER BY n

Will get me numbers from 1 to 100, each in a separate row. A tally table, with partial results shown in this image.

2021-04-19 13_56_15-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

If I change this, and add an OFFSET, I can skip some rows. For example, I can skip 7 rows by adding that clause, as shown below.

2021-04-19 13_58_58-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

If I only want a certain number, say 6 rows, I add the FETCH clause.

2021-04-19 13_59_40-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

This is useful for pagination, saving some network bandwidth, and less buffer space on the client. Not necessarily helping the query processor, but it does make it easy for developers and with small result sets (and source table sizes), this is nice.

It’s a fairly easy clause to use, but it can still require the full work on the server for looking through data, so be sure you read the link in the note above.

SQLNewBlogger

I was testing some code I’d seen from someone and it occurred to me to document the process a bit. I used a tally table, and wrote this around a couple of my experiments.

You can do this as well, show some learning, testing, understanding of code in ten minutes.

About way0utwest

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

1 Response to Basic OFFSET–#SQLNewBlogger

  1. Pingback: OFFSET and FETCH in SQL Server – Curated SQL

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.