Alter SQL Function Referenced by Computed Column

Question

If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove the reference, save the Table, alter the Function, add everything back, and save again. Even small changes are nightmares.

Can you tell SQL Server that you don't care that the Function is being referenced by Formulas and to just go ahead and change the underlying Function?

Additional Details: The computed column is not persisted or referenced by a FK constraint because it is non-deterministic. The function takes into consideration the current time. It's dealing with the question of whether a record is expired or not.

Answer 1

Sorry for this late answer, but it can be useful.

You can use a dummy function for each computed column that will call your real function.

Example:
The computed column use the formula: dbo.link_comp('123')
This function forward the arguments and calls and return the function dbo.link('123') (Your real function)
Both functions just need to use the same arguments and return the same type.

Then, the function that is locked is dbo.link_comp and you can still ALTER dbo.link.
Also, if your function is called from other SQL, you can still use your real function name dbo.link, the dummy function dbo.link_comp is only for the computed column.

Answer 2

I know this is late to the party but I was having this same issue today and didn't find anything which actually solves the issue so I quickly scripted one out.

Essentially it creates a temporary table holding the column info for each computed column using the function, drops the columns from the tables. You then update your function and let it recreate all the columns again with their definitions.

If you have to make changes to the parameters within the definitions (like I need to) you can simply script that part into where the definitions are created again.

If you have computed columns within indexes or other needs you can easily expand on the code but this was beyond the scope of my needs.

Hope it can be useful to someone else.

/* Create temporary table to hold definitions */
CREATE TABLE [#FUNCTION]
(
    [TABLE_NAME] nvarchar(255) NOT NULL,
    [COLUMN_NAME] nvarchar(255) NOT NULL,
    [DEFINITION] nvarchar(255) NOT NULL
)
GO

/* Add data to temp table */
INSERT INTO [#FUNCTION] ( [TABLE_NAME], [COLUMN_NAME], [DEFINITION] )
SELECT TABLE_NAME, COLUMN_NAME, definition FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN sys.computed_columns ON ( object_id = object_id( TABLE_NAME ) AND name = COLUMN_NAME )
WHERE definition LIKE '%MyFunctionName%'
GO

/* Remove columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)

DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME] FROM [#FUNCTION]
OPEN c_CursorName

FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] DROP COLUMN [' + @COLUMN_NAME + ']' )

    FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME
END

CLOSE c_CursorName
DEALLOCATE c_CursorName
GO

/* Update function */
-- Update function here
GO

/* Recreate computed columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)
DECLARE @DEFINITION nvarchar(255)

DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME], [DEFINITION] FROM [#FUNCTION]
OPEN c_CursorName

FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] ADD [' + @COLUMN_NAME + '] AS ' + @DEFINITION )

    FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION
END

CLOSE c_CursorName
DEALLOCATE c_CursorName
GO

/* Remove temp table */
DROP TABLE [#FUNCTION]
GO