Блог

Главная - Вопросы по программированию - Почему набор результатов TOP 1, использующий ORDER BY в пустом столбце, показывает результаты, отличные от TOP 5?

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

#tsql

#tsql

Вопрос:

При запросе таблицы 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;

Я скорее ожидал, что TOP 1 вернет Bravo, поскольку это первая введенная запись. Я также ожидал, что TOP 5 вернет Bravo, за которым следуют Foxtrot, Charlie, Alpha и Delta, поскольку это порядок, в котором были добавлены записи.

То, что я получаю для TOP 1, всегда Foxtrot, а для TOP 5 я получаю Echo, Delta, Alpha, Charlie и Foxtrot. Кажется, что оператор TOP 5 игнорирует первую запись, а затем меняет следующие 5 записей.

Это наблюдение справедливо, если я изменяю TOP 5 на TOP 3, так как тогда я получаю Alpha, Charlie, Foxtrot.

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

Комментарии:

1. Ваш порядок ничего не делает, потому что вы упорядочиваете по одному столбцу, и все поля содержат одинаковые данные в этом столбце. Базы данных не гарантируют сохранение порядка. Вы должны упорядочить по второму столбцу. ORDER BY TestValue, TestName;

2. Если я удаляю операторы ORDER BY из своих примеров, я всегда получаю Bravo в качестве первого результирующего набора — из обоих запросов. Это имеет смысл, поскольку это была первая введенная запись. ORDER BY, похоже, следует другому набору правил, которые влияют на результаты.

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

4. Если вы действительно хотите точно знать, что произошло, вы, конечно, можете проанализировать внутренние структуры таблиц DBCC PAGE и перепроектировать сортировку с помощью теста черного ящика или дизассемблера, но вы не могли бы использовать эти результаты ни для чего, кроме как для удовлетворения своего любопытства. Любой ответ, который точно объясняет, как оптимизатор получает свой результат, опасен в том смысле, что у людей может возникнуть соблазн использовать результаты, когда они могут быть аннулированы при следующем обновлении SQL Server.

Ответ №1:

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

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

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

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

Комментарии:

1. Спасибо. Мой вопрос был вызван скорее любопытством, чем производительностью или бизнес-потребностями. Похоже, я не собираюсь выяснять, почему он делает то, что делает, мне просто нужно принять его таким, какой он есть.

2. Я уже так думал. Да, вещи такие, какие они есть. SQL Server просто хочет получить результаты как можно быстрее. Это зависит от его внутреннего состояния и реализаций. Просто будьте осторожны с ORDER BY . Иногда они необходимы для получения правильных результатов, но когда вы хотите отсортировать результаты только для клиента, вы можете разрешить клиенту (ам) выполнять сортировку. Таким образом, сервер может использовать свою вычислительную мощность для обработки большего количества запросов.