Computed Columns

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

CREATE TABLE dbo.Products

(

    ProductID int IDENTITY (1,1) NOT NULL

  , QtyAvailable smallint

  , UnitPrice money

  , InventoryValue AS QtyAvailable * UnitPrice

);

-- Insert values into the table.

INSERT INTO dbo.Products (QtyAvailable, UnitPrice)

VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.

SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue

FROM dbo.Products;

CREATE TABLE t2 (a int, b int, c int, x float, 

   y AS CASE x 

         WHEN 0 THEN a 

         WHEN 1 THEN b 

         ELSE c 

      END)

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

·         Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

·         A computed column cannot be the target of an INSERT or UPDATE statement.

Note:There is NO way to alter computed column. You will have to drop and recreate it