SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server:
-- Get arbitrarily numbered row_numbers
SELECT ROW_NUMBER() OVER ()
-- Skip arbitrary rows
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
OFFSET 3 ROWS
Strictly speaking, that limitation makes sense because the above
ROW_NUMBER() or
OFFSET expressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then again, any
ORDER BY clause is non-deterministic, if you do not order by a strictly
UNIQUE expression, such as a primary key.
So, that’s a bit of a pain, because other databases aren’t that strict and after all, you might just not care about explicit ordering for a quick, ad-hoc query, so a “reasonable”, lenient default would be useful.
Constant ORDER BY clauses don’t work
You cannot add a constant
ORDER BY clause to window functions either. I.e.:
-- This doesn't work:
SELECT ROW_NUMBER() OVER (ORDER BY 'a')
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY 'a'
OFFSET 3 ROWS
Note that
ORDER BY 'a' uses a constant
VARCHAR expression, not a numeric one, as that would be generating column-reference-by-index expressions, which would be non-constant in the second example.
Random column references don’t work
So you’re thinking that you can just add a random column reference? Sometimes you can, but often you cannot:
-- This doesn't work:
SELECT ROW_NUMBER() OVER (
ORDER BY [no-column-available-here]
)
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY a
OFFSET 3 ROWS
The above examples show that you do not always have a column reference available in any given SQL expression. There is no useful column that you could refer to from the
ROW_NUMBER() function. At the same time, you can write
ORDER BY a in the second example, but only if
a is a “comparable” value, i.e. not a LOB, such as
text or
image.
Besides, as we don’t really care about the actual ordering, is it worth ordering the result set by anything at all? Do you happen to have an index on
a?
Quasi-constant ORDER BY expressions do work
So, to stay on the safe side, if ever you need a dummy
ORDER BY expression in SQL Server, use a quasi-constant expression, like
@@version (or
@@language,
or any of these). The following will always work:
-- This always works:
SELECT ROW_NUMBER() OVER (ORDER BY @@version)
-- So does this:
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY @@version
OFFSET 3 ROWS
From the upcoming
jOOQ 3.4, we’ll also generate such synthetic
ORDER BY clauses that will help you simplify writing vendor-agnostic SQL in these edge-cases, as we believe that you simply shouldn’t think of these things all the time.
