Computed Columns in Sql Server

Friends,

In this post we are gonna look at Computed Columns in Sql Server. Sql server not only allows you to store data in columns of table but also allows you to construct a calculation and include it as a column in the table. The table will store ONLY the formulae you have given for computation. When ever you request for the column then sql server performs the required computation and returns the value. In short the calculations/computed columns are NOT PRECALCULATED. Let’s see how to create a computed column using sql scripts.

Take a new query window and execute the below given query.

CREATE TABLE Sales
(SalesID        INT         IDENTITY(1,1),
OrderDate       DATE        NOT NULL,
SubTotal        MONEY       NOT NULL,
TaxAmount       MONEY       NOT NULL,
ShippingAmount  MONEY       NOT NULL,
GrandTotal      AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate   DATE        NULL)
GO

Once the table is created then insert some rows into the table using insert command. REMEMBER that the calculated column will not take and value as input and if you pass value to calculated column also then it will error out.

Insert into sales values(…,..,..,.,..,…)

Once the data is inserted then select the data using the select command and see the data retreiving for the calculated column.

select * from Sales

This is how you can add a calculated column to the table while creating a table. now let’s us see how to add a Calculated column to an already existing columns. This can be acheived using ALTER command and PFB the example query of the same.

ALTER TABLE Sales
    ADD ProductMargin AS (SubTotal - TaxAmount)

That’s it .. Hope you understood the calculated column concept .. Enjoy coding !!

Regards,
Roopesh Babu V