Pchełki SQL: ROW_NUMBER() bez sortowania

https://xpil.eu/tpr 📋

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.

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;

Tu 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/

https://xpil.eu/tpr 📋