Вычисление SQL Server ROW_NUMBER() OVER() для производной таблицы

В некоторых других базах данных (например, DB2 или Oracle с ROWNUM) я могу опустить предложение ORDER BY в статье ранжирования OVER(). Например:

ROW_NUMBER() OVER()

Это особенно полезно при использовании с упорядоченными производными таблицами, например:

SELECT t.*, ROW_NUMBER() OVER()
FROM (
    SELECT ...
    ORDER BY
) t

Как это можно эмулировать в SQL Server? Я нашел людей, использующих этот трюк, но это неправильно, так как он будет вести себя недетерминистически с уважением к порядку из производной таблицы:

-- This order here ---------------------vvvvvvvv
SELECT t.*, ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (
    SELECT TOP 100 PERCENT ...
    -- vvvvv ----redefines this order here
    ORDER BY
) t

Конкретный пример (как можно видеть на SQLFiddle):

SELECT v, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM (
  SELECT TOP 100 PERCENT 1 UNION ALL
  SELECT TOP 100 PERCENT 2 UNION ALL
  SELECT TOP 100 PERCENT 3 UNION ALL
  SELECT TOP 100 PERCENT 4
  -- This descending order is not maintained in the outer query
  ORDER BY 1 DESC
) t(v)

Кроме того, я не могу повторно использовать какое-либо выражение из производной таблицы для воспроизведения предложения ORDER BY в моем случае, поскольку производная таблица может быть недоступна, поскольку она может быть предоставлена некоторой внешней логикой.

Итак, как я могу это сделать? Могу ли я вообще это сделать?

Ответ 1

Тройка Row_Number() OVER (ORDER BY (SELECT 1)) НЕ должна рассматриваться как способ избежать изменения порядка базовых данных. Это всего лишь средство избежать того, чтобы сервер выполнял дополнительную и ненужную сортировку (он все равно может выполнять сортировку, но это будет стоить минимальной возможной суммы по сравнению с сортировкой по столбцу).

Все запросы на сервере SQL АБСОЛЮТНО ДОЛЖНЫ иметь предложение ORDER BY в самом внешнем запросе, чтобы результаты были надежно упорядочены с гарантированным способом.

Концепция "сохранения исходного порядка" не существует в реляционных базах данных. Таблицы и запросы всегда должны считаться неупорядоченными до тех пор, пока в самом внешнем запросе не будет указано предложение ORDER BY.

Вы можете попробовать один и тот же неупорядоченный запрос 100 000 раз и всегда получать его с тем же заказом, и, таким образом, полагаете, что можете положиться на указанное упорядочение. Но это было бы ошибкой, потому что в один прекрасный день что-то изменится, и у него не будет ожидаемого порядка. Например, когда база данных обновляется до новой версии SQL Server, это вызвало много запросов, чтобы изменить порядок их размещения. Но это не должно быть большим изменением. Что-то вроде добавления или удаления индекса может привести к различиям. И еще: установка пакета обновления. Разбиение таблицы. Создание индексированного представления, которое включает соответствующую таблицу. Достижение некоторой точки опроса, когда вместо поиска выбирается сканирование. И так далее.

Не полагайтесь на результаты, которые вы хотите заказать, если вы не сказали "Сервер, ORDER BY".