Schema-binding with recursive scalar UDF

TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the CREATE OR ALTER syntax? Or is it a bug that other syntaxes are disallowed?

A trivial recursive scalar UDF can be constructed as follows

CREATE FUNCTION dbo.Try1 (@i int)
RETURNS int
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try1(@i - 1));
END;

As long as this is not schema-bound then this is allowed.

Let's try schema-binding it, we'll do

CREATE FUNCTION dbo.Try2 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try2(@i - 1));
END;

Nope.

Cannot find either column "dbo" or the user-defined function
or aggregate "dbo.Try2", or the name is ambiguous

Create it without recursion then alter it

CREATE OR ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try3(@i - 1));
END;

A different error this time:

Cannot schema bind function 'dbo.Try3' because name 'dbo.Try3' is invalid
for schema binding. Names must be in two-part format
and an object cannot reference itself.

Hmmm, an object cannot reference itself who invented that rule? It's not in the docs.

Let's try with CREATE OR ALTER

CREATE OR ALTER FUNCTION dbo.Try4 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try4(@i - 1));
END;

Still not.

But if we first do CREATE without recursion, then do CREATE OR ALTER (not ALTER) with recursion then it works

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try5(@i - 1));
END;

The weird thing is: this doesn't make sense whichever way CREATE OR ALTER works underneath. I

Self-referencing functions are not well documented in general, but the restriction on schema-binding such functions has been around forever. Not every error condition is mentioned in the parent syntax pages.

The particular sequence of events outlined in the question that results in a schema-bound self-referencing function should not be allowed. (It works for multi-statement table-valued functions as well).

Allowing schema-binding for self-referencing functions introduces many new possibilities which would have to be considered and tested. For example, the following use of your function results in a DBCC CHECKTABLE failure:

CREATE TABLE dbo.Test 
(
    n integer NOT NULL,
    -- Can be persisted because the function is deterministic
    -- Checks for determinism require schemabinding
    sn AS dbo.Try5(n) PERSISTED, 
        CHECK (sn > 0)
);

-- Succeeds
INSERT dbo.Test (n) 
VALUES (31);

DBCC CHECKTABLE (N'dbo.Test') 
    WITH EXTENDED_LOGICAL_CHECKS;

Msg 2537, Level 16, State 0, Line 76
Table error: object ID 1137439126, index ID 0, partition ID 72057594050772992,
alloc unit ID 72057594110541824 (type In-row data), page (1:568), row 0.
The record check (valid computed column) failed. The values are 2 and 0.
DBCC results for 'Test'.
There are 1 rows in 1 pages for object "Test".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'Test'
(object ID 1137439126).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (Sandpit.dbo.Test).
DBCC execution completed.
If DBCC printed error messages, contact your system administrator.


I didn't look into the reasons for this failure, but I suppose the CHECKTABLE activity to verify the computed column value ends up exceeding the maximum nesting level of 32.

Many things would need testing and/or tweaking to support schema-binding self-referencing functions. This usage isn't popular enough to justify the investment. Alternatives for recursion exist within SQL Server.

StackExchange Database Administrators Q#325814, answer score: 7