Pchełki SQL: ROW_NUMBER() bez sortowania

Rzecz o tym, jak nie posortować danych w SQL-u.

Każdy, kto przez jakiś czas pracował z językiem SQL wie, że prędzej czy później pojawia się potrzeba ponumerowania kolejnych rekordów. W tym celu wymyślono funkcję ROW_NUMBER(), która – jak sama nazwa wskazuje – generuje kolejne wartości od jeden wzwyż, dla każdego rekordu.

Przykład:

;WITH q1 AS ( SELECT CONVERT(VARCHAR(255), NEWID()) id
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
UNION ALL SELECT CONVERT(VARCHAR(255), NEWID())
)
SELECT ROW_NUMBER() OVER (ORDER BY id) rn
, id
FROM q1;

Powyższe zapytanie wyświetli (ważne: alfabetycznie!) 10 losowych identyfikatorów i ponumeruje je od 1 do 10:

RNID
109878F7F-75E5-418C-971C-1929475FADC8
219535BBA-DE30-4E31-8BFF-75275C3FAC0A
31E62CF95-62D0-4A11-9B97-2B66BE4457AE
430E4103A-1A3A-4D7C-B132-9CD7952DDAEC
57C03409E-C19D-4CC8-B066-771A537E1929
68240BC5D-ECE0-4FA6-8E8C-CC2A1993A1AD
7884C49C7-26F0-40BA-B398-A06910BB4D46
88B35FF0E-D2AD-4C77-8850-56AC9E6EB701
9A05F3F0A-E4CA-47CC-99E7-9803A0406FF4
10 D4306896-ED27-4912-83F7-63D2FB29C210

Jeżeli jednak zerkniemy na plan wykonania tego zapytania zobaczymy, że 100% zasobów zostało zużytych na operację sortowania danych (tak naprawdę PRAWIE 100%, wartości są zaokrąglane do najbliższej całości).

Czasem jednak bywa tak, że owszem, potrzebujemy ponumerować rekordy od jeden wzwyż, ale kompletnie nie interesuje nas ich kolejność. W takim scenariuszu sortowanie elementów to marnowanie zasobów serwera.

Jednak składnia funkcji ROW_ID() wymaga użycia OVER(ORDER BY …). bez tego nasze zapytanie nawet się nie skompiluje.

No i co?

Okazuje się, że można zastosować bardzo prosty (acz na pierwszy rzut oka nieoczywisty) trick, dzięki któremu unikniemy sortowania danych, jednocześnie zachowując zgodność ze składnią języka.

Trick wygląda następująco:

ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

Innymi słowy sortujemy wirtualnie utworzoną pustą kolumnę – i nic ponadto.

Pełne zapytanie z przykładu powyżej, ale już bez sortowania, wygląda tak:

;WITH q1 AS (         SELECT  CONVERT(VARCHAR(255), NEWID()) id
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
            UNION ALL SELECT  CONVERT(VARCHAR(255), NEWID())
)
SELECT    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
        , id
FROM    q1;

Wynik:

RNID
1ECFDBF52-22DC-466A-B87B-82033E01FA2A
29E52B339-BC35-4646-A596-B2B28D273AA2
37E8AAB59-9F5D-457A-AF2E-4D4F8C9A9706
4F7CE5A52-F4C6-4E20-948B-14EAE616F993
501BC815D-3E89-4DD1-9CE3-81B928FF8BFE
604812135-89A9-4699-9D1A-9C8F38900765
765DB014B-808E-4360-A658-BAC0E845333A
8BCCF214E-58B0-42FB-AAF4-B7CFC89F861C
99725EFE4-595E-4F11-A976-3228B37DDBD2
10 BAEB3DCA-01E9-4BEF-9EA3-1AA3595534CD

Jak widać dane są nieposortowane, a plan zapytania się nieco uprościł:

Większość zasobów poszła na operację Constant Scan, czyli „samo gęste”. Nie marnujemy cennych cykli procesora na zbędne sortowanie.

Ciekawe? Więcej pchełek SQL tutaj: http://xpil.eu/k/pchelki-2/pchelki-sql/

Autor: xpil

Po czterdziestce. Żonaty. Dzieciaty. Komputerowiec. Krwiodawca. Emigrant. Rusofil. Lemofil. Sarkastyczny. Uparty. Mól książkowy. Ateista. Apolityczny. Nie oglądam TV. Uwielbiam matematykę. Walę prosto z mostu. Gram na paru instrumentach. Lubię planszówki. Słucham bluesa, poezji śpiewanej i kapel a'capella. || Kliknij tutaj po więcej szczegółów ||

Dodaj komentarz

Bądź pierwszy!

Powiadom o
avatar