sql-server - SQL Server 延迟名称解析适用于函数吗？

Question

SQL Server 具有延迟名称解析功能，请阅读此处了解详细信息: https://msdn.microsoft.com/en-us/library/ms190686(v=sql.105).aspx

在该页面中，它所谈论的都是存储过程，因此似乎延迟名称解析仅适用于存储过程，而不适用于函数，我做了一些测试。

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

请注意表 xxx 不存在。当我执行上面的 CREATE 语句时，我收到以下消息:

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

SQL Server 似乎立即发现了不存在的表xxx，并且证明延迟名称解析不适用于函数。然而，当我稍微改变它如下:

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

我可以成功执行它:

Commands completed successfully.

执行以下语句时:

select dbo.f1(3)

我收到此错误:

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

所以这里表xxx的解析似乎被推迟了。这两种情况之间最重要的区别是返回类型。但是我无法解释延迟名称解析何时适用于函数以及何时不适用。谁能帮助我理解这一点？提前致谢。

Answer 1

感觉您正在寻求理解为什么您的特定示例不起作用。 Quassnoi 的答案是正确的，但没有提供原因，所以我去搜索并找到了这个 MSDN Social answer作者:厄兰·索马斯科格。有趣的部分:

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.

希望有助于理解原因:-)

编辑:

我确实花了一些时间来确认 Quassnoi 的评论，即 sys.columns 以及其他几个表确实包含一些有关内联函数的元数据，因此我不确定是否还有其他元数据未写入。不过，我想我应该添加一些我发现的其他注释，这些注释可能有助于结合解释。

首先引用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.

然后从 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.

因此，内联函数本质上似乎返回一个查询，并且能够使用外部查询对其进行优化，不允许使用黑盒方法，因此不允许延迟名称解析。