Czy RANK () i DENSE_RANK () są deterministyczne czy niedeterministyczne?

Według oficjalnego Microsoft BOL DENSE_RANK jest niedeterministyczny ( RANK () ). Ale według funkcji rankingowych Itzika Ben-Gana „... funkcje RANK () i DENSE_RANK () są zawsze deterministyczne”. Kto ma rację?

Co znalazłem do tej pory: Definicja Microsoftu „Funkcje deterministyczne zawsze zwracają ten sam wynik za każdym razem, gdy są wywoływane z określonym zestawem wartości wejściowych i przy takim samym stanie bazy danych”.

Tak więc w tabelach teorii zbiorów Pracownicy

Employee            Salary
Sue Right            1.00
Robin Page           1.00
Phil Factor          1.00

i pracownicy 2

Employee            Salary
Phil Factor          1.00
Sue Right            1.00
Robin Page           1.00

są takie same. Ale funkcje rankingu zwracają różne wartości:

    CREATE TABLE [dbo].[Employees](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Employees2](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[Employees]
([Employee] ,[Salary])
VALUES
('Sue Right', 1)
, ('Robin Page', 1)
,('Phil Factor', 1 )
GO
INSERT INTO [dbo].[Employees2]
([Employee] ,[Salary])
VALUES
('Phil Factor', 1 )
,('Sue Right', 1)
,('Robin Page', 1)
GO
SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees

SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees2

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees2

Według oficjalnego Microsoft BOL DENSE_RANK jest niedeterministyczny (RANK ()). Ale według funkcji rankingowych Itzika Ben-Gana „... funkcje RANK () i DENSE_RANK () są zawsze deterministyczne”. Kto ma rację?

Obaj mają rację, ponieważ używają różnych zmysłów słowa „deterministyczny”.

Z punktu widzenia optymalizatora SQL Server „deterministyczny” ma bardzo dokładne znaczenie; znaczenie, które istniało przed dodaniem funkcji okna i rankingu do produktu. Dla optymalizatora właściwość „deterministyczna” określa, czy funkcję można dowolnie powielić w jej wewnętrznych strukturach drzewa podczas optymalizacji. Jest to niezgodne z prawem w przypadku funkcji niedeterministycznej.

Deterministyczny oznacza tutaj: dokładna instancja funkcji zawsze zwraca to samo wyjście dla tego samego wejścia, bez względu na to, ile razy jest wywoływana. Z definicji nigdy nie jest to prawdą w przypadku funkcji okienkowania, ponieważ jako funkcja skalarna (jednorzędowa) nie zwraca tego samego wyniku w wierszu lub między wierszami. Mówiąc wprost, ROW_NUMBERna przykładzie:

ROW_NUMBERZwraca różne wartości dla różnych wierszy (z definicji!), Więc dla celów optymalizacyjnych jest niedeterministyczny

To jest sens, którego używa BOL.

Itzik mówi inaczej o determinizmie wyniku jako całości. Nad uporządkowanym zestawem danych wejściowych (z odpowiednim zerwaniem powiązania) wyjście jest sekwencją „deterministyczną”. Jest to poprawna obserwacja, ale nie jest to „deterministyczna” jakość, która jest ważna podczas optymalizacji zapytania.

NTILE()jest interesującym przypadkiem; wydaje się, że ma zastosowanie po sortowaniu (co w przypadku remisu jest pozostawione własnym urządzeniom SQL Server, i jest to zwykle spowodowane przez najbardziej efektywny wybór indeksu do celów sortowania). Możesz dokonać tego deterministycznego, nie zmuszając SQL Server do dokonania tutaj arbitralnego wyboru - dodaj jeden lub więcej przerywników do OVER()klauzuli:

OVER (ORDER BY Salary, Employee)

Zasadniczo musisz uczynić sortowanie unikalnym. Jeśli masz pracowników o tej samej nazwie, być może będziesz musiał wybrać inną kolumnę rozstrzygającą lub dodawać kolumny, dopóki naprawdę nie będzie żadnych powiązań.

W przypadku RANK()i DENSE_RANK()więzi są tak naprawdę kluczowym powodem, dla którego nie można uzyskać różnych wartości. Staraj się nie mylić determinizmu wyniku funkcji z determinizmem kolejności wyników. Jeśli twoje zapytania nie mają ORDER BY, to co nie jest deterministyczne?

1   1   Sue Right
1   1   Robin Page
1   1   Phil Factor

1   1   Phil Factor
1   1   Sue Right
1   1   Robin Page

RANK()i DENSE_RANK()zastosował te same wartości w obu przypadkach, SQL Server właśnie zwrócił wyniki w innej kolejności. Nie ma to nic wspólnego z oczekiwaniem na takie same dane wyjściowe RANK()lub na DENSE_RANK()podstawie tych samych danych wejściowych - chodzi tu tylko o przyjęcie lub oczekiwanie określonej deterministycznej kolejności, gdy powiedziałeś SQL Serverowi (pomijając ORDER BYklauzulę), że nie obchodzi cię kolejność wyniki. Zobacz # 3 tutaj:

• T-SQL Wtorek # 56: Założenia SQL Server

Składnia:

WindowFunction() OVER (PARTITION BY <some expressions>        -- partition list
                       ORDER BY <some other expressions>)     -- order list

Obie funkcje RANK()i DENSE_RANK(), zgodnie z ich definicjami, dają takie same wyniki, o ile same wyrażenia w OVERklauzuli są deterministyczne. I to właśnie miał na myśli Itzik Ben-Gun w swoim artykule. Listy te są najczęściej tylko kolumnami zaangażowanych tabel.

Tak więc, chociaż funkcje są ogólne, nie są deterministyczne, ich implementacja mogła zadbać o rozróżnienie dwóch przypadków i uznać je za deterministyczne lub nie, po zbadaniu list partycji i kolejności.

Zgaduję, że programiści SQL-Server zdecydowali, że łatwiej jest je zaimplementować, jak zawsze „niedeterministyczni”, mimo że jest to sprzeczne z ich definicją funkcji deterministycznych. Są one zatem określane jako niedeterministyczne w MSDN, ponieważ w bieżącej implementacji silnik uważa je zawsze za niedeterministyczne.

Jeszcze jeden argument jest, że dwie inne funkcje okna, ROW_NUMBER()i NTILE()są jeszcze bardziej skomplikowane, ponieważ dla nich mający identyczną moc, wyrażenie w partycji i porządku przez list nie tylko musi być deterministyczny, ale wyjątkowy, jak również. Wdrażanie tych wszystkich szczegółów nie jest jednak trywialne.

^{Nie będę komentował kolejności zestawów wyników, ponieważ nie ma to nic wspólnego z determinizmem, co jasno wyjaśnił Aaron Bertrand w swojej odpowiedzi.}