Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans:


If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

Are there any other advantages of SCHEMABINDING a function, even if it does not access data?

Yes.

Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question).

This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every function invocation (since the unbound function could change at any time).

There are five important function properties:

• Determinism

• Precision

• Data Access

• System Data Access

• System Verification

For example, take the following unbound scalar function:

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

We can look at the five properties using a metadata function:

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

The two data access properties have been set true, and the other three are set false.

This has implications beyond those that might be expected (use in indexed views or indexed computed columns, for example).
Effects on the query optimizer

The Determinism property in particular affects the query optimizer. It has detailed rules concerning the types of rewrites and manipulations it is allowed to perform, and these are very much restricted for non-deterministic elements. The side-effects can be quite subtle.

For example, consider the following two tables:

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

...and a query that uses the function (as defined previously):

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

The query plan is as expected, featuring a seek into table T2:

However, if the same logical query is written using a derived table or common table expression:

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;

The execution plan now features a scan, with the predicate involving the function stuck in a Filter:

This also happens if the derived table or common table expression is replaced by a view or in-line function. A FORCESEEK hint (and other similar attempts) will not succeed:

The fundamental issue is that the query optimizer cannot reorder nondeterministic query elements as freely.

To produce a seek, the Filter predicate would need to be moved down the plan to the T2 data access. This movement is prevented when the function is non-deterministic.
Fix

The fix for this example involves two steps:

• Add WITH SCHEMABINDING

• Make the function deterministic

The first step is trivial. The second involves removing the non-deterministic implicit cast from string to datetime; replacing it with a deterministic CONVERT. Neither is sufficient on its own.

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

The function properties are now:

With the optimizer freed up, all examples now produce the desired seek plan.

Note that using a CAST to datetime in the function would not work, because it is not possible to specify a conversion style in that syntax:

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

This function definition produces the scan plan, and the properties show it remains non-deterministic:

StackExchange Database Administrators Q#140381, answer score: 97