Does SQL Server deferred name resolution work for functions?

user_170931 0
Viewed 1107

SQL Server has Deferred Name Resolution feature, read here for details: https://msdn.microsoft.com/en-us/library/ms190686(v=sql.105).aspx

In that page, all it's talking is stored procedure so it seems Deferred Name Resolution only works for stored procedures and not for functions and I did some testing.

create or alter function f2(@i int)
returns table
as
    return (select fff from xxx)
go

Note the table xxx does not exist. When I execute the above CREATE statement, I got the following message:

Msg 208, Level 16, State 1, Procedure f2, Line 4 [Batch Start Line 22]
Invalid object name 'xxx'.

It seems that SQL Server instantly found the non-existent table xxx and it proved Deferred Name Resolution doesn't work for functions. However when I slightly change it as follows:

create or alter function f1(@i int)
returns int
as 
begin
    declare @x int;
    select @x = fff from xxx;
    return @x
end
go

I can successfully execute it:

Commands completed successfully.

When executing the following statement:

select dbo.f1(3)

I got this error:

Msg 208, Level 16, State 1, Line 34
Invalid object name 'xxx'.

So here it seems the resolution of the table xxx was deferred. The most important differences between these two cases is the return type. However I can't explain when Deferred Name Resolution will work for functions and when not. Can anyone help me to understand this? Thanks in advance.

function sql-server
2 Answers

It feels like you were looking for understanding of why your particular example didn't work. Quassnoi's answer was correct but didn't offer a reason so I went searching and found this MSDN Social answer by Erland Sommarskog. The interesting part:

However, it does not extend to views and inline-table functions. For stored procedures and scalar functions, all SQL Server stores in the database is the text of the module. But for views and inline-table functions (which are parameterised view by another name) SQL Server stores metadata about the columns etc. And that is not possible if the table is missing.

Hope that helps with understanding why :-)

EDIT:

I did take some time to confirm Quassnoi's comment that sys.columns as well as several other tables did contain some metadata about the inline function so I am unsure if there is other metadata not written. However I thought I would add a few other notes I was able to find that may help explain in conjunction.

First a quote from Wayne Sheffield's blog:

In the MTVF, you see only an operation called “Table Valued Function”. Everything that it is doing is essentially a black box – something is happening, and data gets returned. For MTVFs, SQL can’t “see” what it is that the MTVF is doing since it is being run in a separate context. What this means is that SQL has to run the MTVF as it is written, without being able to make any optimizations in the query plan to optimize it.

Then from the SQL Server 2016 Exam 70-761 by Itzik Ben-Gan (Skill 3.1):

The reason that it's called an inline function is because SQL Server inlines, or expands, the inner query definition, and constructs an internal query directly against the underlying tables.

So it seems the inline function essentially returns a query and is able to optimize it with the outer query, not allowing the black-box approach and thus not allowing deferred name resolution.

user_64900590

What you have in your first example is an inline function (it does not have BEGIN/END).

Inline functions can only be table-valued.

If you used a multi-statement table-valued function for you first example, like this:

CREATE OR ALTER FUNCTION
        fn_test(@a INT)
RETURNS @ret TABLE
        (
        a INT
        )
AS
BEGIN
        INSERT
        INTO    @ret
        SELECT  a
        FROM    xxx
        RETURN
END

, it would compile alright and fail at runtime (if xxx would not exist), same as a stored procedure or a scalar UDF would.

So yes, DNR does work for all multi-statement functions (those with BEGIN/END), regardless of their return type.

user_551590
Related