Schema-binding with recursive scalar UDF

Question

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. If it actually drops and re-creates then why don't we get the first error? And if it alter then why does this last option work?

The funniest bit: If we create it without schema-binding first, then do CREATE OR ALTER WITH SCHEMABINDING then we get

Cannot schema bind function 'dbo.Try6'. 'dbo.Try6' is not schema bound.

Which is just utter nonsense.

db<>fiddle

Answer 1

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.