Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

SELECT TOP 99.99999999999999 PERCENT...


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:

a,b,x,z

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:

x,z,b,a

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

a,b,x,z

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).
Opublikowane 26 maja 2010 20:04 przez marekpow
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems