Menu
Does anyone know of a way to alter a computed column without dropping the column in SQL Server. I want to stop using the column as a computed column and start storing data directly in the column, but would like to retain the current values.
Is this even possible?
373
However, it is a usual perception that you can alter any computed column with the help of SQL Server management Studio (SSMS) without dropping it. Incorrect syntax near the keyword 'As'. Frankly speaking there is no way you can alter any computed column without dropping it.
First remove the current computed column. Then add the new computed column (with same name.) Unfortunately, in order to change a computed column, you must DROP and re-CREATE the column. Actual Table Column order is inconsequential.
A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.
A computed column is a virtual column whose value is calculated from other values in the table. By default, the expression's outputted value is not physically stored. Instead, SQL Server runs the expression when the column is queried and returns the value as part of the result set.
Not that I know of but here is something you can do
add another column to the table update that column with the values of the computed column then drop the computed column
115
If you need to maintain the name of the column (so as not to break client code), you will need to drop the column and add back a stored column with the same name. You can do this without downtime by making the changes (along the lines of SQLMenace's solution) in a single transaction. Here's some pseudo-code:
begin transaction drop computed colum X add stored column X populate column using the old formula commit transaction
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
