Статьи

Уловка SQL Server: отсутствует условие ORDER BY

Март 29, 2019

Известно, что SQL Server очень строго интерпретирует стандарт SQL. Например, следующие выражения или операторы невозможны в SQL Server:

1
2
3
4
5
6
7
-- 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

Строго говоря, это ограничение имеет смысл, поскольку вышеприведенные ROW_NUMBER() или OFFSET недетерминированы. Два последующих выполнения одного и того же запроса могут привести к разным результатам. Но с другой стороны, любое предложение ORDER BY является недетерминированным, если вы не упорядочиваете по строго UNIQUE выражению, такому как первичный ключ.

Так что это немного болезненно, потому что другие базы данных не так уж строги и, в конце концов, вы можете просто не заботиться о явном упорядочении для быстрого специального запроса, поэтому «разумный», мягкий по умолчанию будет полезен.

Постоянные предложения ORDER BY не работают

Вы не можете добавить постоянное предложение ORDER BY в оконные функции. То есть:

1
2
3
4
5
6
7
8
-- 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

Обратите внимание, что ORDER BY 'a' использует постоянное выражение VARCHAR , а не числовое, поскольку это будет генерировать выражения столбец-ссылка-по-индексу, которые будут непостоянными во втором примере.

Случайные ссылки на столбцы не работают

Итак, вы думаете, что вы можете просто добавить случайную ссылку на столбец? Иногда вы можете, но часто вы не можете:

01
02
03
04
05
06
07
08
09
10
-- 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

Приведенные выше примеры показывают, что вы не всегда имеете ссылку на столбец в любом выражении SQL. Нет полезного столбца, к которому вы могли бы обратиться из функции ROW_NUMBER() . В то же время вы можете написать ORDER BY a во втором примере, но только если a является «сопоставимым» значением, т.е. не большим объектом, таким как text или image .

Кроме того, поскольку нас не волнует фактическое упорядочивание, стоит ли вообще заказывать результат, установленный чем-либо? У вас случайно есть индекс на?

Квазиконстантные выражения ORDER BY работают

Поэтому, чтобы оставаться в безопасности, если вам когда-либо понадобится фиктивное выражение ORDER BY в SQL Server, используйте квазипостоянное выражение, например @@version (или @@language , или любое из них ). Следующее всегда будет работать:

1
2
3
4
5
6
7
8
-- This doesn't work:
SELECT ROW_NUMBER() OVER (ORDER BY @@version)
 
-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY @@version
OFFSET 3 ROWS

В следующем выпуске jOOQ 3.4 мы также создадим такие синтетические предложения ORDER BY , которые помогут вам упростить написание независимого от поставщика SQL в этих крайних случаях, поскольку мы считаем, что вам просто не следует постоянно думать об этих вещах.

Ссылка: Подсказка по SQL Server: отсутствует условие ORDER BY от нашего партнера по JCG Лукаса Эдера в блоге JAVA, SQL и AND JOOQ .
34
Share: