Pomimo ogólnej wiedzy, że nie stosuje się klauzuli ORDER BY w widokach często zdarza się, że chcemy lub też musimy jej użyć.
Przy pierwszej próbie stworzenia prostego widoku z ORDER BY dostajemy błąd.
Co za tym się kryje? Jak rozwiązać ten problem, czy da się to go naprawić ?
W pierwszym kroku należy utworzyć tabelę do testów i wypełnić ją danymi.
CREATE TABLE dbo.FOO
( ID INT PRIMARY KEY
, MyValue INT UNIQUE);
GO
INSERT INTO dbo.FOO (ID,MyValue) VALUES (1,3);
INSERT INTO dbo.FOO (ID,MyValue) VALUES (2,2);
INSERT INTO dbo.FOO (ID,MyValue) VALUES (3,1);
GO
Następnie przystępujemy do utworzenia widoku:
CREATE VIEW vw_FOO(ID,MyValue)
AS
SELECT ID
,MyValue
FROM dbo.FOO
ORDER BY ID DESC
GO
Przy próbie utworzenia widoku vw_FOO wygenerowany zostaje następujący komunikat błędu:
Komunikat informuje o sytuacjach, w których stosowanie klauzuli ORDER BY jest dozwolone. Jest to np. zastosowanie w zapytaniu tworzącym klauzuli TOP lub FOR XML, wraz z klauzulą ORDER BY. Jeśli w tabeli znajduje się znana liczba wierszy niezmiennych w czasie (np. 1000), to w celu stworzenia posortowanego widoku można użyć klauzuli TOP (1000). W przypadku zmieniającej się liczby wierszy sensowniej jest zastosowanie klauzuli TOP (100) PERCENT, która wyświetla 100% (czyli wszystkie) wiersze z tabeli.
W wyniku takiego połączenia klauzula ORDER BY uzyskuje nowe znaczeniejako części filtra TOP.
Przypominając składnię TOP
[ TOP (expression) [PERCENT] [ WITH TIES ]
]
Dokładne informacje na ten temat można znaleść pod tym adresem http://technet.microsoft.com/en-us/library/ms189463.aspx
Oczywiste więc staje się użycie następującego kodu do utworzenia widoku oraz wywołanie go:
CREATE VIEW dbo.vw_FOO
AS SELECT TOP (100) PERCENT
ID
, MyValue
FROM FOO ORDER BY ID DESC
GO
SELECT ID
, MyValue
FROM dbo.vw_FOO
GO
Działa, ale... hmmm... dziwne ?
Utworzyliśmy widok z ORDER BY i TOP, ale wynik nasz świadczy o tym, że klauzula ORDER BY nie zadziałała. Sprawdzimy samo zapytanie.
SELECT TOP 100 PERCENT ID
, MyValue
FROM FOO
ORDER BY ID DESC
GO
Jak widać wykonanie zapytania z ORDER BY i TOP nie działa wyłącznie w widoku.
Klauzula ORDER BY może (ale nie musi) zostać zignorowana przez optymalizator SQL Server 2005 lub SQL Server 2008.
Jest to znany błąd w MS SQL Server 2008 gdzie w wyniku stosowania klauzul ORDER BY i TOP w widokach uzyskujemy losowy układ wierszy.
Ale dlaczego tak się dzieje ?
Jeżeli przyjrzymy się planom wykonania to zauważymy, że nie ma w nich sortowania. Tworząc oryginalną tabelę FOO zostaje ona utworzona jako sterta (heap) (no clustered index) i nie tworzy ona pomocniczego indeksu. Patrząc na plan wykonania zapytania zauważymy, że występuje tylko Table Scan. W tym szczególnym przypadku optimizer stwierdza, że klauzula TOP 100 PERCENT całkowicie pokrywa wszystkie wiersze i nie musi wszystkiego przeliczać. Stąd też mamy brak sortowania.
Można to rozwiązać na dwa sposoby:
Rozwiązanie nr 1
Pierwszy sposób polega na użyciu w klauzuli TOP wartości 99.9999 PERCENT
CREATE VIEW dbo.vw_FOO
AS
SELECT TOP (99.99999) PERCENT ID
, MyValue
FROM dbo.FOO
ORDER BY ID DESC
GO
ale i tutaj musimy uważać gdyż w zależności od liczby wierszy w tabeli możemy uzyskać inną ilość wyświetlanych wierszy w widoku. Łatwo to sprawdzić stosując prosty kod:
SELECT COUNT(*) ViewCount
FROM vw_FOO
GO
SELECT COUNT(*) OriginalCount
FROM dbo.FOO
GO
Jeżeli uzyskamy mniejszą ilość wierszy dla widoku musimy zwiększyć precyzję w klauzuli TOP dopisując więcej cyfr 9 po przecinku (kropce).
Rozwiązanie nr 2
Należy zainstalować Hot-fix
"FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order"
http://support.microsoft.com/kb/926292http://support.microsoft.com/kb/956717/
Linki:
http://blogs.msdn.com/b/queryoptteam/archive/2006/03/24/560396.aspx
http://blog.sqlauthority.com/2009/11/24/sql-server-interesting-observation-top-100-percent-and-order-by/
http://support.microsoft.com/kb/926292
http://support.microsoft.com/kb/956717/
http://technet.microsoft.com/en-us/library/ms189463.aspx