Join Class

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])


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.


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.

Hire SQL Developer
Computed Columns in SQL Server
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
SQL Computed Column
MS SQL Examples | Join MS SQL Course