Computed Column in SQL Server

A computed column is computed from an expression that can use other columns in the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators but the sub query can’t be used for computed column.

For example to calculate the total price we are using Quantity and price

Total price = Quantity * price

There are two type of Computed Column

Ø  Persisted

Ø  Non-Persisted

By default computed column is none persisted.

Creating a table

Create table tblOrder ( OrderID bigint, Qty int, Price decimal(18,2), Total_Price as Qty * price persisted )

Now I am going to insert some records.

INSERT INTO tblOrder(OrderID,Qty,Price) Values (1,10,12.5), (2,5,130.35), (3,100,50.5), (4,54,80)

See the above sql query, in this query I am inserting only OrderID,Qty,Price the value of total_Price will be calculated.

Persisted Computed Column

Persisted computed columns are run whenever data is inserted or is updated in a table. A persisted computed field occupies memory for the data. Comparing to a persisted computed column is faster than a non-persisted computed column.

Non-Persisted Computed Column 

Non-Persisted computed columns are run whenever data is selected from a table. A Non-Persisted computed field does not occupy memory for the data, because it is executed when the data is selected. Comparisons to non-persisted computed columns are slower than persisted computed columns. By default computed column is non persisted means if we don't write keyword persisted then computed column treat as a non-persisted. 

We can’t insert the value in computed column.

We can’t update the computed column value.

 By marking a computed column as PERSISTED, we can create an index on a computed column. Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

Limitations of Computed Column:

• A computed column cannot be the target of an INSERT or UPDATE statement.
  
  
• We can’t reference columns from other tables for a computed column expression directly.
  
  
• Null ability for a computed column value will be determined by the database engine itself. The result of most expressions is considered null able even if only non- nullable columns are present, because possible underflows or overflows will produce null results as well. To overcome this problem the COLUMNPROPERTY function with the Allow Null property.
  
  ‘Sub query can’t be used as an expression for creating a computed column.
  
  b. If we are using different data types in our expression then operator of lower precedence will try to convert into the higher precedence data type. If implicit conversion is not possible then error will be generated.

We can create a table using GUI

Right click on the table and create a new table

New window will be open.

Here we can declare the computed expression.