Computed Columns

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

            (QUANTITY, COST)

VALUES (10, 1200.00)           

GO

SELECT * FROM ComputedColumns

Result set

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