sql-server add tag
Paul White (imported from SE)
It is well-known that `SCHEMABINDING` a function can [avoid an unnecessary spool][1] 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?

  [1]: https://blogs.msdn.microsoft.com/sqlprogrammability/2006/05/12/improving-query-plans-with-the-schemabinding-option-on-t-sql-udfs/
Top Answer
Paul White
### 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).

## Function properties

There are **five** important function properties:

* Determinism
* Precision
* Data Access
* System Data Access
* System Verification

For example, take the following unbound scalar function:

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

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

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

[![Result][1]][1]

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:

```sql
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):

```sql
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:

[![Seek plan][2]][2]

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

```sql
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:

[![Scan plan][3]][3]

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:

[![Error message][4]][4]

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:

1. Add `WITH SCHEMABINDING`
2. 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**.

```sql
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:

[![New properties][5]][5]

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

### `CAST` limitation

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:

```sql
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:

[![CAST function properties][6]][6]

  [1]: https://i.stack.imgur.com/L2c9Z.png
  [2]: https://i.stack.imgur.com/CpR1H.png
  [3]: https://i.stack.imgur.com/VdlJH.png
  [4]: https://i.stack.imgur.com/ZLMrR.png
  [5]: https://i.stack.imgur.com/cL0du.png
  [6]: https://i.stack.imgur.com/g88Xg.png

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.