When you create a computed column in SQL Server, the expression you use for the column will either be deterministic or nondeterministic. This can have implications, such as whether or not you can use it in an index or flag it as “persisted”.

A deterministic column is one that will return the same value for a specific set of input values and given the same state of the database. A nondeterministic column can return a different value even when given the same input even if the database state remains the same. For example, a function that returns the current date is nondeterministic, because it will return a different value each day.

You can use the COLUMNPROPERTY() function with the IsDeterministic argument to find out whether or not a computed column is deterministic.

Continue reading →

When you create a computed column in SQL Server, you have the option of flagging it as “persisted”. A persisted computed column is one that is physically stored in the table. If you don’t specify that it’s persisted, then the column’s value will be calculated each time you run a query against it.

You can query the sys.computed_columns system catalog view to find out whether a computed column is marked as persisted.

Continue reading →

In SQL Server, you can use T-SQL to get the definition for a computed column by querying the sys.computed_columns system catalog view.

Continue reading →

This article demonstrates how to use T-SQL to change an existing column to a computed column in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Continue reading →

This article demonstrates how to use T-SQL to add a computed column to an existing table in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Continue reading →

This article demonstrates how to use T-SQL to create a computed column in SQL Server.

A computed column is a virtual column that uses an expression to calculate its value. The expression will typically use data from other columns. A computed column is not physically stored in the table unless it’s marked PERSISTED.

Continue reading →