Contents
What is a persisted computed column?
Computed columns can be persisted. It means that SQL Server physically stores the data of the computed columns on disk. When you change data in the table, SQL Server computes the result based on the expression of the computed columns and stores the results in these persisted columns physically.
Can we update computed column?
A: There is NO way to alter computed column. You will have to drop and recreate it. Here is a demonstration of it. If you try to alter the computed column it will throw following error.
Is it possible to create a persisted computed column here?
Msg 4936, Level 16, State 1, Line 42 Computed column ‘UTCTime’ in table ‘test’ cannot be persisted because the column is non-deterministic. I think I’m following the deterministc rules here. Is it possible to create a persisted computed column here?
Do you need to persist a column in SQL Server?
If it is so you can index it, you don’t need to persist a column to index it… You need to use a deterministic style when converting from a string representation. You were not using a deterministic style with the conversion from string to date. You were unnecessarily specifying a style when converting from date to datetime2.
How is the nullibility of a computed column determined?
Nullibility for a computed column value will be determined by the database engine itself. The result of a non-nullable referenced column may be NULL in certain conditions to avoid possible overflows or underflows. You can provide an alternate value for NULL using the ISNULL (check_expression, constant), if required.
When to use persisted property in SQL Server?
You may have noticed that we also used the property “Persisted” for our computed column. This property for computed columns has been introduced in SQL Server 2005 and onwards. It is important for any computed column, because many additional features depend on it. To be able to make a computed column as Persisted it has to be deterministic.