Computed column in sql table example

Apart from all standard data type, Sql table also support a data type called computed column, which is actually a column type of computed value.

Computed column value always calculated automatically based on other column values, means whenever we insert or update the values of some specified column, the computed value is automatically updated.

Let’s look at how to define computed column in sql table!

    CREATE TABLE tbProduct
   (
      Pid int IDENTITY (1,1) NOT NULL,
      QtyAvail smallint,
      UnitPrice money,
      StockValue AS QtyAvail * UnitPrice
    );

Now in above table StockValue is the computed column, which will be updated automatically based on other two column values (QtyAvail * UnitPrice).

Let’s insert some value.

INSERT INTO [dbo].[tbProduct]
([QtyAvail] ,[UnitPrice])
VALUES
(10,15.24)

GO

As you can see, in insert query, we don’t need to specify the computed column value, after inserting the values in above column, computed column value will be automatically there as per specification.

ALTER TABLE [tbProduct] ADD TaxedValue AS (QtyAvail * UnitPrice * 1.5);

We are adding another new computed column called “TaxedValue”.

Note: we cannot use computed value data to create another computed column data. Means we cannot write alter query like example below.

Trying to create another computed column [TaxedValue1], this will not work:

ALTER TABLE [tbProduct] ADD TaxedValue1 AS (QtyAvail * UnitPrice * StockValue);

Produce an error like: Computed column 'StockValue' in table 'tbProduct' is not allowed to be used in another computed-column definition.

We can use standard drop statement to drop and recreate a computed column.

ALTER TABLE tbProduct DROP COLUMN TaxedValue;

Alternate solution to Computed Column in SQL.

Instead of creating multiple computed columns in sql table, which hold physical data, we could have created a sql view with all required formula, which would be considered as better design, easy to deal with multiple situation and calculating different values.

However, if you need just one calculated column, then creating that in the table itself would good idea.