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