ORDER BY, OFFSET, and Fetch in TSQL

on June 29, 2018

My most recent Quizletter featured a quiz on ORDER BY in TSQL, complete with questions on the OFFSET and FETCH clauses which we got in SQL Server 2012.

One great thing about teaching TSQL School is that it reminds me of these cool options that can be easy to forget.

Overall Quiz Stats

Folks did pretty well on this quiz, but lots of people did fall into one mean little trap that I left in one question. (Sorry! I can be tricksy.) Here are the stats at the time of writing this post:


Entries: 436	Average Score (Out of 4): 2.64	Average Percentage: 66%

Now for the fun stuff, let’s talk about answers!

Question 1

1: How will the results of this query be ordered (and why)?

SELECT PersonID FROM Application.People;

Correct: 76% Answer: No ordering is guaranteed in this case

This is very important to know: although the answers may come back ordered by PersonID in this case, you cannot count on that always being the case, and it could change at any point. Ordering of results is never guaranteed unless you explicitly use an ORDER BY in your query.

Question 2

2: What will this ORDER BY do?

SELECT PersonID, FullName FROM Application.People ORDER BY 1 ASC;

Correct: 90% Answer: Order the results by the first column listed, PersonID

Numbers used like this in the ORDER BY clause represent the column position. I was surprised that so many people knew the answer to this one, to be honest. It is a little bit troubling, because Microsoft calls out using numbers as positional representations in order by as an anti-pattern that should be avoided.

But, I confess, I personally do use this myself when I’m writing quick and dirty code (and sometimes forget to fix it, too).

Question 3

3: What will this OFFSET / FETCH clause do?

SELECT FullName FROM Application.People OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Correct: 26% Answer: This query will throw a syntax error

Why? You can only use OFFSET with an ORDER BY.

(This ain’t no TOP operator which doesn’t require ORDER BY, this is a part of ORDER BY.)

I asked this question to try to get a sense of how many people are currently using the OFFSET clause already in code and would pick out the goof based on familiarity. Seems like not many! That’s OK, but it’s good to know how this works for those times when it may come in handy.

Question 4

4: What will this OFFSET clause do?

SELECT FullName FROM Application.People ORDER BY PersonID OFFSET 10 ROWS;

Correct: 72% Answer: Return all the rows except for the 10 with the lowest PersonIDs

You are allowed to specify OFFSET without a FETCH NEXT. In this case, we are ordering by PersonID. We didn’t specify ascending or descending, so the default ascending sort order is used for the order. We are OFFSET-ing the first 10 rows ASC, so that’s the lowest 10 PersonIDs. We’ll get all the other rows, because we haven’t limited how many will be fetched.