Почему набор результатов ТОП 1 с использованием ORDER BY в пустом столбце показывает результаты, отличные от результатов ТОП 5?

При запросе таблицы SQL Server и ORDER BY в столбце, заполненном только «», я получаю разные результаты для TOP 1 по сравнению с TOP 5. Кажется, что первая запись игнорируется, а затем возвращается запрошенное количество записей - в обратном порядке.

-- Drop temp table if it exists
DROP TABLE IF EXISTS [tempdb]..[#OrderByTest];

-- Create temp table
CREATE TABLE [#OrderByTest]
(
    TestValue VARCHAR(1),
    TestName  VARCHAR(20)
 );

-- Populate temp table
INSERT INTO [#OrderByTest] (TestValue, TestName) VALUES
            ('', 'Bravo'),
            ('', 'Foxtrot'),
            ('', 'Charlie'),
            ('', 'Alpha'),
            ('', 'Delta'),
            ('', 'Echo'),
            ('', 'Golf'),
            ('', 'Hotel'),
            ('', 'Indigo'),
            ('', 'Juliet');

--SELECT * FROM #OrderByTest;

-- Query table for TOP 1 and ORDER BY
SELECT TOP 1 TestName FROM #OrderByTest ORDER BY TestValue;

-- Query table for TOP 5 and ORDER BY
SELECT TOP 5 TestName FROM #OrderByTest ORDER BY TestValue;

Я скорее ожидал, что ТОП-1 вернет Браво, так как это первый введенный рекорд. Я также ожидал, что ТОП-5 вернет Браво, за которым следуют Фокстрот, Чарли, Альфа и Дельта, поскольку это порядок, в котором были добавлены записи.

То, что я получаю в ТОП-1, - это всегда фокстрот, а в ТОП-5 я получаю Эхо, Дельта, Альфа, Чарли и Фокстрот. Кажется, что оператор TOP 5 игнорирует первую запись, а затем обращает вспять следующие 5 записей.

Это наблюдение верно, если я изменю ТОП-5 на ТОП-3, с тех пор я получаю Альфа, Чарли, Фокстрот.

Есть идеи о том, что происходит?


tsql
person renMike    schedule 11.04.2019    source источник
comment
Ваш заказ ничего не делает, потому что вы заказываете по одному столбцу, и все поля имеют одинаковые данные в этом столбце. Базы данных не гарантируют сохранность порядка. Заказывать следует по второму столбцу. ORDER BY TestValue, TestName;   -  person Cleptus    schedule 11.04.2019
comment
Если я удаляю операторы ORDER BY из своих примеров, я всегда получаю Bravo в качестве первого набора результатов - из обоих запросов. Это имеет смысл, поскольку это была первая введенная запись. ORDER BY, похоже, следует другому набору правил, которые действительно влияют на результаты.   -  person renMike    schedule 11.04.2019
comment
Поскольку нет возможности детерминированно упорядочить идентичные значения, оптимизатор может использовать любой порядок, который ему нравится, и возвращать любые строки, которые ему нравятся. Вы увидите еще один результат, если измените таблицу, сделав TestName первичным ключом, хотя мы вообще не упоминаем об этом в запросе. Все эти заказы действительны. Тот факт, что этот порядок является стабильным при одинаковой структуре таблицы и данных, не должен вызывать особого удивления (алгоритм не является произвольным), но вы тоже не можете рассчитывать на это.   -  person Jeroen Mostert    schedule 11.04.2019
comment
Если вы действительно хотите знать точно, что произошло, вы, безусловно, можете проанализировать внутренние структуры таблиц с помощью DBCC PAGE и реконструировать сортировку с помощью теста черного ящика или дизассемблера, но вы не можете использовать эти результаты для чего угодно, кроме удовлетворения вашего любопытства. Любой ответ, который объясняет, как именно оптимизатор получает результат, опасен в том смысле, что у людей может возникнуть соблазн использовать результаты, когда они могут быть признаны недействительными при самом следующем обновлении SQL Server.   -  person Jeroen Mostert    schedule 11.04.2019

Ответы (1)


arrow_upward
0
arrow_downward

Это типичный пример того, как SQL Server определяет свой собственный порядок записей, когда не указан явный порядок. Это также учитывается для всех записей, которые кажутся «равными» с точки зрения упорядочивания. (В данном случае: все записи.)

Итак, вы указали порядок, но он не имеет логического эффекта, поскольку все записи «равны» в соответствии с указанным вами порядком. Таким образом, в конечном итоге SQL Server сам определяет порядок.

Почему он вообще отличается от запроса без упорядочивания? Или отличается от запроса, который использует связанные функции, такие как TOP, но с другими значениями параметров? Об этом могут знать только инженеры Microsoft. Вероятно, это связано со сложной внутренней реализацией механизма SQL Server.

И, строго говоря, это вообще не имеет значения. В противном случае вы все равно обеспечили бы эффективный заказ. ;) И по соображениям производительности: если заказ не нужен, не надо его поставлять.

person Bart Hofland    schedule 11.04.2019
comment
Спасибо. Мой вопрос был задан из любопытства, а не из соображений производительности или деловой необходимости. Похоже, я не собираюсь выяснять, почему он делает то, что делает, мне просто нужно принять это таким, какое оно есть. - person renMike; 11.04.2019
comment
Я уже так думал. Да, вещи такие, какие есть. SQL Server просто хочет получить результаты как можно быстрее. Это определяет его внутреннее состояние и реализации. Только будьте осторожны с ORDER BY. Иногда они необходимы для получения правильных результатов, но если вы хотите только отсортировать результаты для клиента, вы можете позволить клиенту (-ам) выполнить сортировку. Таким образом, сервер может использовать свою вычислительную мощность для обработки большего количества запросов. - person Bart Hofland; 11.04.2019