Alter a Table-Valued Function in SQL Server

You can alter a table-valued function (TVF) in SQL Server using the ALTER FUNCTION T-SQL syntax.

You simply follow ALTER FUNCTION with the new definition. Basically it’s like using CREATE FUNCTION except you replace CREATE with ALTER.

Example

Here’s an example to demonstrate:

ALTER 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

If this function didn’t already exist, and I was creating it for the first time, I’d use the following code:

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

It’s exactly the same, except it begins with CREATE instead of ALTER.

Drop and Recreate

Another way to do it is to drop the function and create it again:

DROP FUNCTION IF EXISTS udf_CatsByName_ITVF;
GO

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

In this case, I used the DIE syntax (Drop If Exists), which means I won’t get an error if the object doesn’t already exist before trying to drop it.

Inline TVFs vs Multi-Statement TVFs

The examples on this page use an inline table-valued function. The same concept applies to multi-statement TVFs.

See Create an Inline Table-Valued Function and Create a Multi-Statement Table-Valued Function for more detailed examples of each one.