An example of such a feature are the “computed” and “persisted” columns.
As explained on MSDN BOL, computed columns’ values are computed by expressions that can use other columns in the same table.
Also, when using the PERSISTED keyword with a COMPUTED column, the data of the computed column is physically stored in the table.
Let’s see some code and try out this great feature!
First let’s create two tables representing product information:
–Note the column with name “totalPrice”: It is a computed column which computes
–the total price (price + VAT)
CREATE TABLE Product1
id int, code varchar(50),
totalPrice as (price+(price*(vatPerc/100)))
–Note again the column with name “totalPrice”: It is the same computed column as in table
–Product 1 with the difference that it uses the PERSISTED keyword.
CREATE TABLE Product2
totalPrice as (price+(price*(vatPerc/100))) PERSISTED
———— Testing Table Product1 ————–
INSERT INTO Product1 (id,code,price,vatPerc)
SELECT 1, ‘PRODUCT01’,100,15
SELECT 2, ‘PRODUCT02’,200,15
* I know, I could use a Table Value Constructor (Row Constructors) but I would prefer that my examples to be compatible with all versions of SQL Server as Table Value Constructors is one of the new programmability features in SQL Server 2008 🙂
Note that in the above INSERT statement I am not inserting any values for the “totalPrice” column. Though, when taking a look at the generated results, the totalPrice values are calculated on the fly!
———— Testing Table Product2 ————–
INSERT INTO Product2 (id,code,price,vatPerc)
SELECT 1, ‘PRODUCT03’,300,15
SELECT 2, ‘PRODUCT04’,400,15
Recommended eBooks on SQL Server: