Wednesday, August 19, 2015

"Altering Computed Column" Bug or Feature?

At some point I wanted to change a formula within calculated column.

At first, I've tried to use ALTER TABLE ... ALTER COLUMN to do so, but got a syntax error.

Then I've tried to look in MSDN and there is mentioning of "<computed_column_definition>" only in "ADD" column section and nothing in the "ALTER COLUMN"

I was tied in time and simply dropped the column and then created a new one instead.

What the problem?

Now I think that is not a SQL Server bug but a feature, which protects persisted data in computed caolumn.

Would say SQL allow me to alter the defining formula for a computed column. It might easily happen that some of calculated values would be created by one rule and some by another, which is completely not acceptable.

However, that is true only for Persisted calculated column, which has it's values physically stored within a table and it has nothing with not-Persisted one, which calculated on the fly.

In my assumption Microsoft developers just simplified their job by not segregating Persisted and not-Persisted calculated columns and not allowing to alter any computed column at all.

No comments:

Post a Comment