Sql server alter computed column formula

Create a free Team Why Teams? Or you could rename the table to something else, drop the computed column, and create a VIEW in place of the original table i. 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. Stack Overflow for Teams — Start collaborating and sharing organizational knowledge.

Have you indexed the columns?

sql server - Alter SQL Function Referenced by Computed Column - Stack Overflow

Even small changes are nightmares. 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. Sorted by: Reset to default. Persisted it? I'm having the same issue. You then update your function and let it recreate all the columns again with their definitions.

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? Example: The computed column use the formula: dbo. With Computed Column we have to explicitly name all columns, which loses us that safety net. 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.

An Essential Guide to SQL Server Computed Columns By Examples

Used it in a view with schemabinding? 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. The function takes into consideration the current time. Highest score default Trending recent votes count more Date modified newest first Date created oldest first.

I agree this is a BIG pain! It's dealing with the question of whether a record is expired or not. Learn more about Collectives. Then, the function that is locked is dbo. Also, if your function is called from other SQL, you can still use your real function name dbo. Viewed 11k times. 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.

Learn more about Teams. We've had to work around Computed Columns enough times to have decided they are more trouble than they gain. Find centralized, trusted content and collaborate around the technologies you use most. Essentially it creates a temporary table holding the column info for each computed column using the function, drops the columns from the tables.

Asked 14 years, 5 months ago. Take our short survey. Modified 8 years, 1 month ago.

ALTER TABLE computed_column_definition (Transact-SQL)

Foreign key relationship to it? Feeling it right now! Connect and share knowledge within a single location that is structured and easy to search. Additional Details: The computed column is not persisted or referenced by a FK constraint because it is non-deterministic. Please help us improve Stack Overflow.

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. Obviously you could keep the old table, drop the computed column, and create a separate VIEW that contained the computed column. Add a comment. How are we doing?