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

No, as far as I know, you cannot do this - you'll have to first remove all computed columns referencing a function, alter the function, and then recreate the computed columns.

Maybe MS will give us a "CREATE OR ALTER FUNCTION" command in SQL Server 2010/2011? :-)

Marc

Answer 2

You could try with some good schema compare tool, that create the script for you :)

Answer 3

The consequences of the ALTER could be huge.

Have you indexed the columns? Used it in a view with schemabinding? Persisted it? Foreign key relationship to it?

What if the ALTER changes the datatype, NULLability or determinism?

It's easier to stop ALTER FUNCTION with dependencies than deal with so many scenarios.

Answer 4

You could change the column to be not-computed, and update it by TRIGGER.

Or you could rename the table to something else, drop the computed column, and create a VIEW in place of the original table (i.e. with the original table name) and including the "computed" column you need.

EDIT: note that this may mess with your INSERTs into the original table name (now a VIEW). Obviously you could keep the old table, drop the computed column, and create a separate VIEW that contained the computed column.

We've had to work around Computed Columns enough times to have decided they are more trouble than they gain. Fail-saf inserts(1), trying to insert into VIEWs onto tables with computed columns, things that require messing with SET ARITHABORT and so on.

(1) We have fail-safe inserts like:

INSERT INTO MyTable SELECT * FROM MyOtherTable WHERE ...

which are designed to fail if a new column is added one table and not the other. With Computed Column we have to explicitly name all columns, which loses us that safety net.