This article illustrate about the feature of computed columns.
A table can have one or more computed columns. It is an expression defining the value of the specified columns.
Let's take an example
CREATE TABLE ComputedColumns
QUANTITY INT NULL,
COST MONEY NULL,
INVESTMENT AS (QUANTITY * COST)
We're creating a table named "ComputedColumns" and setting 3 columns "Quantity", "Cost" and "Investment"
Because of the multiply of Quantity and Cost will lead us to Investment costs, it would be useful if we define Investment as variable storing the data of multiplication of these 2 variables.
INSERT INTO ComputedColumns
VALUES (10, 1200.00)
SELECT * FROM ComputedColumns
QUANTITY COST INVESTMENT
10 1200.00 12000.00
After this process, it automatically calculates the data as you can see.
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.
Hope you like it.
Posted by: MR. JOYDEEP DAS