In SQL Server, it’s usually a good idea to schema bind your user-defined functions (UDFs).

Schema binding your UDF will ensure that the underlying tables can’t be changed in a way that would affect your function. Without schema binding, the underlying tables or other objects could be modified or even deleted. Doing this could break the function.

To create a schema bound UDF, use the WITH SCHEMABINDING in your T-SQL code for creating the function. This applies whether the function is a scalar function or a table-valued function (TVF).

In any case, I’ve included examples of an inline TVF, a multi-statement TVF, and a scalar function.

Example 1 – Inline Table-Valued Function

Here’s an example of creating an inline TVF with schema binding:

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO

Notice I used the two-part name when referencing the table in my query (I used dbo.Cats when referencing the table, instead of just Cats). Doing this is a requirement for schema binding an object. If you try to schema bind an object without using two-part names you’ll get an error.

Now that I’ve schema bound my function, if I try to drop the table referenced in its definition, I get an error:

DROP TABLE Cats;

Result:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'cats' because it is being referenced by object 'udf_CatsByName_ITVF'.

Here’s what happens if I try to create the function without using two-part naming:

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM Cats
    WHERE CatName = @CatName
    );

GO

Result:

Msg 4512, Level 16, State 3, Procedure udf_CatsByName_ITVF, Line 7
Cannot schema bind table valued function 'dbo.udf_CatsByName_ITVF' because name 'Cats' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Example 2 – Multi-Statement Table-Valued Function

With multi-statement TVFs, you place WITH SCHEMABINDING after the return variable specification.

CREATE FUNCTION dbo.udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
    WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @pets
    SELECT 
        CONCAT('Cat', ' ', CatId),
        CatName
    FROM dbo.Cats
    WHERE CatName = @PetName;

    INSERT INTO @pets
    SELECT 
        CONCAT('Dog', ' ', DogId),
        DogName
    FROM dbo.Dogs
    WHERE DogName = @PetName;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO @pets
        VALUES (
            '',
            'There are no pets of that name.'
            )
    END

    RETURN;
END;

GO

Example 3 – Scalar Function

Here’s a scalar function example:

CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)  
RETURNS smallint
WITH SCHEMABINDING
AS  
BEGIN
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM dbo.Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;

GO

Example 4 – Adding Multiple Arguments

You can specify multiple arguments as a comma-separated list. For example, if you want to specify schema binding and encryption, then you’ll need to add these as a comma separated list.

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
    WITH SCHEMABINDING, ENCRYPTION
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO