Existe algum benefício em SCHEMABINDING uma função além da proteção de Halloween?

Sim.

Deixar de especificar WITH SCHEMABINDINGsignifica que o SQL Server ignora as verificações detalhadas que normalmente faz no corpo da função. Ele simplesmente marca a função como acessando dados (conforme mencionado no link fornecido na pergunta).

Esta é uma otimização de desempenho. Se não fizesse essa suposição, o SQL Server teria que realizar as verificações detalhadas em cada chamada de função (uma vez que a função não vinculada poderia mudar a qualquer momento).

Existem cinco propriedades de funções importantes:

• Determinismo
• Precisão
• Acesso de dados
• Acesso a dados do sistema
• Verificação do sistema

Por exemplo, considere a seguinte função escalar não vinculada:

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

Podemos olhar para as cinco propriedades usando uma função de metadados:

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);

As duas propriedades de acesso a dados foram definidas como true e as outras três foram definidas como false .

Isso tem implicações além das esperadas (uso em exibições indexadas ou colunas computadas indexadas, por exemplo).

Efeitos no otimizador de consulta

A propriedade Determinism em particular afeta o otimizador de consulta. Possui regras detalhadas sobre os tipos de reescritas e manipulações que pode realizar, e estas são muito restritas para elementos não determinísticos. Os efeitos colaterais podem ser bastante sutis.

Por exemplo, considere as duas tabelas a seguir:

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

...e uma consulta que usa a função (conforme definido anteriormente):

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

O plano de consulta é o esperado, apresentando uma busca na tabela T2:

No entanto, se a mesma consulta lógica for escrita usando uma tabela derivada ou uma expressão de tabela comum:

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;

O plano de execução agora apresenta um scan, com o predicado envolvendo a função preso em um Filter:

Isso também acontece se a tabela derivada ou a expressão de tabela comum for substituída por uma exibição ou função em linha. Uma FORCESEEKdica (e outras tentativas semelhantes) não terá sucesso:

A questão fundamental é que o otimizador de consulta não pode reordenar elementos de consulta não determinísticos tão livremente .

Para produzir uma busca, o predicado Filtro precisaria ser movido para baixo no plano para o acesso aos dados T2. Este movimento é impedido quando a função não é determinística.

Fixar

A correção para este exemplo envolve duas etapas:

1. AdicionarWITH SCHEMABINDING
2. Torne a função determinística

O primeiro passo é trivial. A segunda envolve a remoção da conversão implícita não determinística de string para datetime; substituindo-o por um determinístico CONVERT. Nenhum dos dois é suficiente por si só .

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

As propriedades da função agora são:

Com o otimizador liberado, todos os exemplos agora produzem o plano de busca desejado .

Observe que usar um CASTto datetimena função não funcionaria, pois não é possível especificar um estilo de conversão nessa sintaxe:

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

Esta definição de função produz o plano de varredura e as propriedades mostram que ele permanece não determinístico: