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
