SQL Server – Wielki Mit: porządek wierszy w klastrowanej tabeli

Dzisiaj zaskoczył mnie wątek na forum portalu WSS.pl, w którym okazało się, że kilku uczestników dyskusji żyło w przeświadczeniu, że zapytanie do tabeli, w której jest założony indeks klastowany, w przypadku braku jawnego sortowania (klauzuli ORDER BY) zwróci rekordy w kolejności podyktowanej kluczem indeksu klastrowanego. O ile można wymyśleć wyrafinowane kontrprzykłady tej tezy (we wspomnianym wątku warto przeczytać ze zrozumieniem wypowiedź Marcina Szeligi), o tyle Marek Adamczuk podał w trakcie dyskusji przykład, który jest banalny do odtworzenia i o wiele szybciej otwiera oczy osobom, które szerzą wspomniany mit :-)

Weźmy taką tabelę:

IF OBJECT_ID('dbo.SortTest') IS NOT NULL
  DROP TABLE dbo.SortTest;
CREATE TABLE dbo.SortTest (
  id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  col1 int NOT NULL,
  col2 int NOT NULL
);
GO

Wstawmy do niej trochę rekordów:

INSERT INTO dbo.SortTest (col1, col2)
SELECT number, number + 1
FROM master.dbo.spt_values
WHERE type = 'P' AND number <= 2047
ORDER BY number DESC;
GO

Ważne, by zauważyć, że wartości kolumny col2 maleją wraz ze wzrostem wartości w kolumnie id, która jest kolumną klucza głównego oraz indeksu klastrowanego (przy czym należy pamiętać, że indeks klastrowany a klucz główny to zupełnie dwie różne bajki i to, że akurat w tym przykładzie się pokrywają, to przypadek szczególny).

Weźmy zapytanie:

SELECT col2, id FROM dbo.SortTest;

Zapytanie być może zwróci nam wynik posortowany po kolumnie id (mamy fart! często wcale tak nie będzie, ale do tego dojdziemy za moment).

Teraz dodajmy indeks nieklastrowany na kolumnie col2:

CREATE INDEX IX_SortTest_col2 ON dbo.SortTest (col2);

I ponownie wykonajmy nasz SELECT (wcześniej włączmy pokazywanie graficznego planu wykonania):

SELECT col2, id FROM dbo.SortTest;

Niespodzianka? Wiersze posortowane względem kolumny col2?! :-) Plan wykonania nie pozostawia złudzeń – mamy skan indeksu IX_SortTest_col2 (dość oczywisty wybór optymalizatora – IX_SortTest_col2 to teraz najmniejszy indeks pokrywający zapytanie, czyli zawierający wszystkie kolumny, o które pytamy). I teraz dalej pytanie – czy to gwarantuje, że od teraz nasz SELECT będzie zawsze zwracał wiersze posortowane względem kolumny col2? Niekoniecznie! Zakonotujmy i uznajmy za prawdę objawioną – jeżeli nie używamy w zapytaniu klauzuli ORDER BY, nie możemy oczekiwać żadnego porządku wierszy.

To samo dotyczy sortowania wierszy na siłę w definicjach widoków i funkcji tabelarycznych. Zapomnijmy o takiej opcji! Co prawda (póki co!) działają chwyty typu TOP (baaaaardzo_dużaaaa_liczbaaaa) + ORDER BY, ale jutro mogą przestać działać. Ja sam nie chciałbym używać w swoich bazach danych kodu, o którym nie wiem, czy nie jest wrażliwy na instalację nowego Service Packa do SQL Servera :-) Wystrzegać się zatem takich tricków i sortować po bożemu:

SELECT * FROM Widok ORDER BY KolumnaWidoku;
SELECT * FROM Funkcja ORDER BY KolumnaFunkcji;

Over.