Почему сортировка записей в таблице не соответствует кластеризованному индексу?

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

Вы можете принудительно использовать кластеризованный индекс:

SELECT * FROM TRIAL WITH (INDEX(UNQ))

И вы, вероятно, получите:

и набор результатов:

Id  Name
1   a
5   b
2   c
3   d

Но делать этого особо не стоит, ведь заказ все равно не гарантируется. Если вы хотите, чтобы ваши результаты были отсортированы по некоторым столбцам, сделайте это явно!

Я скопирую фрагмент из книги Exam 70-461: Querying Microsoft SQL Server 2012, где вы можете получить хорошее объяснение:

Может показаться, что вывод отсортирован по empid, но это не гарантируется. Что может быть более запутанным, так это то, что если вы повторно запускаете запрос, кажется, что результат продолжает возвращаться в том же порядке; но опять же, это не гарантировано. Когда механизм базы данных (в данном случае SQL Server) обрабатывает этот запрос, он знает, что может возвращать данные в любом порядке, поскольку нет явных инструкций для возврата данных в определенном порядке. Возможно, из-за оптимизации и других причин ядро ​​базы данных SQL Server на этот раз решило обрабатывать данные определенным образом. Есть даже некоторая вероятность того, что такой выбор будет повторяться, если физические обстоятельства останутся прежними. Но есть большая разница между тем, что может произойти из-за оптимизации и других причин, и тем, что на самом деле гарантировано.

Ядро базы данных может — и иногда изменяет — варианты выбора, которые могут повлиять на порядок, в котором возвращаются строки, зная, что это можно сделать свободно. Примеры таких изменений в выборе включают изменения в распределении данных, доступность физических структур, таких как индексы, и доступность ресурсов, таких как процессоры и память. Кроме того, с изменениями в движке после обновления до более новой версии продукта или даже после применения пакета обновлений аспекты оптимизации могут измениться. В свою очередь такие изменения могли повлиять, в том числе, на порядок строк в результате.

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

РЕДАКТИРОВАТЬ на основе комментариев:

Дело в том, что даже если вы используете кластеризованный индекс, он может возвращать неупорядоченный набор. Предположим, у вас есть физический порядок сгруппированных ключей, например (1, 2, 3, 4, 5). В большинстве случаев вы получите (1, 2, 3, 4, 5), но могут быть ситуации, когда оптимизатор решит выполнить параллельное чтение и скажет, что у него есть 2 параллельных чтения и он читает (1, 2, 3) и (4, 5). Теперь может случиться так, что сначала будет возвращено (4, 5), а затем может быть возвращено (1, 2, 3). Если у вас нет пункта order by, движок не будет тратить свои ресурсы на заказ этого набора и даст вам (4, 5, 1, 2, 3). Таким образом, это объясняет, почему вы всегда должны убедиться, что у вас есть пункт order by, когда вы хотите заказать.

Повторите: таблицы SQL представляют неупорядоченные наборы. Наборы результатов SQL неупорядочены, если только запрос не содержит условие order by.

Итак, если вы хотите, чтобы данные были в порядке, используйте order by:

select t.*
from trial t
order by t.name;

Если вам нужны результаты в определенном порядке, используйте order by. SQL Server имеет хороший оптимизатор. Если он может использовать индекс для запроса — чтобы избежать фактической сортировки — тогда он обычно будет использовать индекс.