Czy jest jakaś korzyść z SCHEMABINDINGU funkcji wykraczającej poza Halloween Protection?

Powszechnie wiadomo, że SCHEMABINDINGfunkcja może uniknąć niepotrzebnego buforowania w planach aktualizacji:

Jeśli używasz prostych UDF T-SQL, które nie dotykają żadnych tabel (tj. Nie mają dostępu do danych), upewnij się, że określiłeś tę SCHEMABINDINGopcję podczas tworzenia UDF. Spowoduje to związanie schematu UDF i zapewni, że optymalizator zapytań nie wygeneruje niepotrzebnych operatorów buforowania dla planów zapytań obejmujących te UDF.

Czy są jakieś inne zalety SCHEMABINDINGfunkcji, nawet jeśli nie ma ona dostępu do danych?

sql-server functions

— Paul White
źródło

Tak.

Brak określenia WITH SCHEMABINDINGoznacza, że SQL Server pomija szczegółowe kontrole, które zwykle wykonuje w treści funkcji. Po prostu oznacza funkcję jako dostęp do danych (jak wspomniano w linku podanym w pytaniu).

Jest to optymalizacja wydajności. Jeśli nie przyjmie tego założenia, SQL Server będzie musiał przeprowadzić szczegółowe kontrole przy każdym wywołaniu funkcji (ponieważ niezwiązana funkcja może się zmienić w dowolnym momencie).

Istnieje pięć ważnych właściwości funkcji:

Determinizm
Precyzja
Dostęp do danych
Dostęp do danych systemowych
Weryfikacja systemu

Na przykład weź następującą niezwiązaną funkcję skalarną:

CREATE FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
AS
BEGIN
    RETURN '19000101';
END;

Możemy spojrzeć na pięć właściwości za pomocą funkcji metadanych:

SELECT 
    IsDeterministic = OBJECTPROPERTYEX(Func.ID, 'IsDeterministic'),
    IsPrecise = OBJECTPROPERTYEX(Func.ID, 'IsPrecise'),
    IsSystemVerified = OBJECTPROPERTYEX(Func.ID, 'IsSystemVerified'),
    UserDataAccess = OBJECTPROPERTYEX(Func.ID, 'UserDataAccess'),
    SystemDataAccess = OBJECTPROPERTYEX(Func.ID, 'SystemDataAccess')
FROM (VALUES(OBJECT_ID(N'dbo.F', N'FN'))) AS Func (ID);

Dwie właściwości dostępu do danych zostały ustawione na true, a pozostałe trzy na false .

Ma to implikacje wykraczające poza te, których można się spodziewać (na przykład w widokach indeksowanych lub indeksowanych kolumnach obliczeniowych).

Wpływ na optymalizator zapytań

W szczególności właściwość Determinizm wpływa na optymalizator zapytań. Ma szczegółowe zasady dotyczące rodzajów przepisywania i manipulacji, które wolno wykonywać, i są one bardzo ograniczone dla elementów niedeterministycznych. Skutki uboczne mogą być dość subtelne.

Weźmy na przykład następujące dwie tabele:

CREATE TABLE dbo.T1
(
    SomeInteger integer PRIMARY KEY
);
GO
CREATE TABLE dbo.T2
(
    SomeDate datetime PRIMARY KEY
);

... i zapytanie korzystające z funkcji (jak zdefiniowano wcześniej):

SELECT * 
FROM dbo.T1 AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = dbo.F(T1.SomeInteger);

Plan zapytań jest zgodny z oczekiwaniami i obejmuje wyszukiwanie w tabeli T2:

Jeśli jednak to samo zapytanie logiczne zostanie zapisane przy użyciu tabeli pochodnej lub wspólnego wyrażenia tabelowego:

WITH CTE AS
(
    SELECT *, dt = dbo.F(T1.SomeInteger) 
    FROM dbo.T1 AS T1
)
SELECT * 
FROM CTE
JOIN dbo.T2 AS T2
    ON T2.SomeDate = CTE.dt;

-- Derived table
SELECT
    *
FROM 
(
    SELECT *, dt = dbo.F(T1.SomeInteger)
    FROM dbo.T1 AS T1
) AS T1
JOIN dbo.T2 AS T2
    ON T2.SomeDate = T1.dt;

Plan wykonania zawiera teraz skanowanie, z predykatem obejmującym funkcję zablokowaną w filtrze:

Dzieje się tak również wtedy, gdy tabela pochodna lub wspólne wyrażenie tabelowe zostanie zastąpione funkcją widoku lub funkcją wbudowaną. FORCESEEKPodpowiedź (i innych podobnych prób) nie uda:

Podstawową kwestią jest to, że optymalizator zapytań nie może dowolnie zmieniać kolejności niedeterministycznych elementów zapytania .

Aby wygenerować wyszukiwanie, predykat Filtr musiałby zostać przeniesiony w dół planu do dostępu do danych T2. Ruchowi temu zapobiega się, gdy funkcja jest niedeterministyczna.

Naprawić

Poprawka dla tego przykładu obejmuje dwa kroki:

Dodaj WITH SCHEMABINDING
Uczyń funkcję deterministyczną

Pierwszy krok jest trywialny. Drugi polega na usunięciu niedeterministycznego ukrytego rzutowania z łańcucha na datetime; zastępując go deterministycznym CONVERT. Samo to nie wystarcza .

ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CONVERT(datetime, '19000101', 112);
END;

Właściwości funkcji są teraz:

Po zwolnieniu optymalizatora wszystkie przykłady tworzą teraz pożądany plan wyszukiwania .

Zauważ, że użycie CASTdo datetimew funkcji nie działałoby, ponieważ nie można określić stylu konwersji w tej składni:

ALTER FUNCTION dbo.F
(
    @i integer
)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
    -- Convert with a deterministic style
    RETURN CAST('19000101' AS datetime);
END;

Ta definicja funkcji tworzy plan skanowania, a właściwości pokazują, że pozostaje on niedeterministyczny:

— Paul White
źródło