Different types of Function in Object_ID

Question

When creating functions, I tend to preface the creation with an existence check

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

GO
CREATE FUNCTION myfunc...

There are several types of object_id to be checked for functions :

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

I have some questions :

What is the difference between 'IF', 'TF' and 'FT' type ?
It is very error-prone to use
```
IF Object_ID(N'myFunc', 'IF') IS NOT NULL
```
as sometime the function is scalar, other cases it is table-valued etc. so I may miss the correct type check.

I see people recommending

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'))

Will it suffice for checking all type of functions ? What is your recommendation ?

IF acts like macro and it is efficient, multiline TF is slower, you should use IF when possible — Lukasz Szozda, Sep 13 '15 at 11:20
What's wrong with information schema tables ? (see "routine") — David Brabant, Sep 13 '15 at 11:22
@lad2025 : When I create CREATE FUNCTION 'func' RETURNS TABLE RETURN(..) is it a IF or a TF? — Kenny, Sep 13 '15 at 11:24

Lukasz Szozda · Accepted Answer · 2015-09-13T11:57:05.347

1) IF vs multiline TF vs CLR TF

Inline Table Valued Function

Acts like macro, very efficient can be treated like parametrized view

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

Multi Statement Table Valued Function

More flexible you can do many intermediate steps if needed but it will be slower than 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 Table-Valued Functions

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) Checking if function exists:

Check ROUTINES catalog:

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' )

score 1 · Answer 2 · answered Sep 13 '15 at 18:40

Add the schema name and remove the object type. Why do you need to check if the object is a function? Think this through:

if object_id(N'dbo.functionname') is not null
drop function dbo.functionname
go
create function dbo.functionname ...

... will drop the function if it exists, then create it.

If there is already an object named "dbo.functionname" and it is NOT a function, the drop will fail. But that's true whether or not you check that "dbo.functionname" is a function or not.

In other words, if there is a TABLE (or any other non-function object) named dbo.functionname, then

if object_id(N'dbo.functionname', N'IF') -- equates to false
drop function dbo.functionanme -- doesn't get run
go
create function dbo.functionname... -- generates error

would fail on the create function (because the table dbo.functionname would not be dropped).

You can't have two objects with the same schema.objectname. So if you just check for object_id(N'schema.objectname'), you should be fine.

Different types of Function in Object_ID

2 Answers2

1) IF vs multiline TF vs CLR TF

2) Checking if function exists: