Alter SQL Function Referenced by Computed Column

sql-server-2005 calculated-columns sql-function non-deterministic
colithium picture colithium · Feb 12, 2009 · Viewed 9.7k times · Source

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

marc_s picture marc_s · Feb 12, 2009

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

Related questions

SQL Server - INSERT failed because of 'ARITHABORT'

I use NHibernate and SQL Server 2005 and I have an index on a computed column in one of my tables. My problem is that when I insert a record to that table I get the following error: INSERT failed because …

Read More
Computed column should result to string

Here is a snap of my database. Both col1 and col2 are declared as int. My ComputedColumn currently adds the Columns 1 and 2, as follows... col1 col2 ComputedColumn 1 2 3 4 1 5 Instead of this, my ComputedColumn should join the columns 1 and 2 (includimg the '-' …

Read More
SQL Server 2005 Computed Column Is Persisted

I have some computed columns in a table and need to know if I should set Is Persisted to true. What are the advantages? Are there any disadvantages? What does 'Is Persisted' mean?

Read More