The purpose of schema binding a user-defined function (UDF) is to ensure that the base objects referenced in the UDF cannot be modified in a way that would affect the function’s definition.
This is fine, as long as you don’t need to make any changes to the underlying objects. But what if you do need to make changes?
You can remove schema binding from a UDF, make the changes to the base object/s, then reapply schema binding.
There are two ways to remove the binding of a user-defined function to its base objects:
- Alter the function so that its definition no longer specifies schema binding.
- Drop the function (then re-create it without schema binding if required).
Example of a Schema-Bound Function
First, here’s an example of a schema bound function:
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;
We know it’s a schema bound function because it contains WITH SCHEMABINDING
in its definition. To remove schema binding, all we need to do is remove that bit.
Option 1 – Alter the Function
To remove schema binding from this function by altering it, we can use the following code:
ALTER FUNCTION dbo.ufn_CountAlbums (@ArtistId int)
RETURNS smallint
AS
BEGIN
DECLARE @AlbumCount int;
SELECT @AlbumCount = COUNT(AlbumId)
FROM dbo.Albums
WHERE ArtistId = @ArtistId;
RETURN @AlbumCount;
END;
All we did was change CREATE
to ALTER
, and remove WITH SCHEMABINDING
.
Option 2 – Drop the Function
Here’s an example of dropping the function, then re-creating it without schema binding:
DROP FUNCTION IF EXISTS dbo.ufn_CountAlbums;
GO
CREATE FUNCTION dbo.ufn_CountAlbums (@ArtistId int)
RETURNS smallint
AS
BEGIN
DECLARE @AlbumCount int;
SELECT @AlbumCount = COUNT(AlbumId)
FROM dbo.Albums
WHERE ArtistId = @ArtistId;
RETURN @AlbumCount;
END;
In this case, I used the DROP IF EXISTS syntax, which prevents an error from occurring in the event the function doesn’t exist.