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.