Jak działa ta instrukcja SELECT TOP wie każdy programista SQL. Ale ja chcę się skupić na pewnym jego przypadku użycia. Na początek przygotujmy dane testowe:
declare @t table(x varchar(100))
insert into @t values
('x'),('z'),('b'),('a')
select top 100 percent * from @t order by x
|
Instrukcja SELECT TOP zwraca rekordy uporządkowane w sposób następujący:
I tu wszystko jest OK. Ale ja pomyślałem o zastosowaniu opcji SELECT TOP szczególnie w
subquery, gdzie jak wiadomo użycie ORDER BY nie jest dopuszczalne. Sprawdźmy to:
select * from (select * from @t order by x) q
|
Wykonanie takiego zapytania kończy się wyjątkiem:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
|
Ale skoro mogę zastosować TOP to spróbujmy zastosować najbardziej nasuwający się sposób użycia zwracający wszystkie dane uporządkowane w
subquery czyli SELECT TOP 100 PERCENT:
select * from (select top 100 percent * from @t order by x) q
|
Ale ze zdziwieniem stwierdzamy, że zapytanie zwraca rekordy w porządku:
Czyli w porządku, w jakim dane te występują w tabeli @t. Zobaczmy wobec tego jak wygląda plan zapytania dla tego przypadku:
Czyli operacja sortowania wcale nie zachodzi!
Zróbmy w takim razie niewielka zmianę w zapytaniu:
| select * from (select top 99.99 percent *
from @t order by x) q |
ale tym razem otrzymujemy zgodnie z oczekiwaniami uporządkowanie
i plan zapytania wyraźnie pokazuje zastosowanie sortowania
i sortowanie zachodzi aż do wartości granicznej dla klauzuli TOP:
| select * from (select top 99.9999999999999 percent
*
from @t order by x) q |
Z tego planu wykonania wynika, że "dołożenie" kolejnej 9-tki po przecinku do wielkości TOP ... PERCENT spowoduje zaokrąglenie do 100 i ponowny "zanik" sortowania w
subquery. Czy można jednak zapewnić, że sortowanie będzie miało jednak miejsce w
subquery i jednocześnie zostaną zwrócone wszystkie rekordy? Można. Wystarczy zamiast stosować PERCENT podać maksymalna ilość wierszy zwracanych przez @@ROWCOUNT_BIG czyli zastosować następujące zapytanie:
select * from (select top 9223372036854775807 * from @t order by x) q
|
Wszystkie powyższe rozważania odnoszą się również to wyrażeń CTE, wewnątrz których nie można również stosować ORDER BY (bez użycia klauzuli TOP). Zastosowanie klauzuli TOP 100 PERCENT zachowuje się podobnie jak w
subquery czyli nie sortuje danych:
;with cte as
(
select top 100 percent x
from @t
order by x
)
select * from cte
|
Lecz mają zastosowanie wszystkie pozostałe opisane powyżej sposoby na uzyskanie sortowania wewnątrz wyrażenia CTE z zastosowaniem klauzuli TOP (w rozwinięciu dziesiętnym i opcją PERCENT oraz z podaniem maksymalnej liczby bigint w serwerze SQL).