sql-server - Object_ID 中不同类型的函数

Question

在创建函数时，我倾向于在创建前先检查是否存在

IF Object_ID(N'myfunc', N'IF') IS NOT NULL 
DROP FUNCTION myfunc

GO
CREATE FUNCTION myfunc...

object_id有几种类型要检查功能:

FN = SQL scalar function
IF = SQL inline table-valued function
TF = SQL table-valued-function
FT = Assembly (CLR) table-valued function

我有一些问题:

1. 'IF'、'TF' 和 'FT' 类型有什么区别？

2. 使用起来很容易出错

   IF Object_ID(N'myFunc', 'IF') IS NOT NULL
   

   有时函数是标量，其他情况下它是表值等。所以我可能会错过正确的类型检查。

我看到有人推荐

IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'myfunc') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

是否足以检查所有类型的函数？你有什么建议？

Answer 1

1) IF 对比多行 TF 对比 CLR TF

内联表值函数

行为像宏，非常高效，可以像参数化 View 一样对待

CREATE FUNCTION dbo.name(@param INT)
RETURNS TABLE
AS 
RETURN 
   SELECT ...
   FROM tab t
   WHERE t.Col = @param;
GO

多语句表值函数

更灵活，如果需要，您可以执行许多中间步骤，但它会比 IF 慢。

CREATE FUNCTION dbo.name()
RETURNS @Result TABLE
(Col_name    INT         NOT NULL,
...
)
AS
BEGIN
    /* Many operations */

    INSERT @Result
    SELECT *
    FROM ...

    RETURN
END
GO

CLR 表值函数

From MSDN

Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned.

In contrast, CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

2) 检查函数是否存在:

检查 ROUTINES目录:

Returns one row for each stored procedure and function that can be accessed by the current user in the current database.

IF EXISTS ( SELECT  1
            FROM INFORMATION_SCHEMA.ROUTINES
            WHERE Specific_schema = 'dbo'
              AND specific_name = 'Foo'
              AND Routine_Type = 'FUNCTION' )