Calculated columns in SQL Server were added way back in SQL Server 2005 and have always if used correctly been a great way to enforce consistent calculations.
Calculated columns are added in the same way as regular columns the only difference is instead of specifying a data type and default value we specify a calculation. As an example below is a create table statement that will create a table called OrderLines and we have some regular columns with the last column being a calculated one which simply multiplies QtyOrdered by UnitCost.
CREATE TABLE dbo.OrderLines ( OrderNum int IDENTITY (1,1) NOT NULL , QtyOrdered int , UnitCost decimal(18,2) , Product nvarchar(30) , Description nvarchar(50) , ExtendedCost AS QtyOrdered * UnitCost );
Note that our calculated column ExtendedCost has no data type specified just the calculation. The value for this calculation is not stored meaning that each time a SELECT statement makes a call for this column SQL Server will do the calculation for us before returning the result set, depending on the size of your result set or the calculation involved then this may cause a slight performance issue, there is an option to store or persist the calculated value as if you would any other column and we will take a look at that next.
As mentioned above we do have the option to store the value of a calculated column so that when doing a retrieval operation SQL Server reads the data from disk rather than it being required to do a re-computation. The calculated column will then be recalculated and stored each time a data change operation occurs on the record.
To mark a calculated column so that it stores or persists its value we use the PERSISTED keyword after the declaration, the below example will add an additional column to our OrderLines table called RetailValue again a very simple example but this time we will use the PERSISTED keyword to store our calculated value.
ALTER TABLE dbo.OrderLines ADD RetailValue AS (QtyOrdered * UnitCost * 1.5) PERSISTED;
Now when we make a retrieval call to the RetailValue column on our OrderLines table the result will be read from the store along with the other columns.
We can also use calculated columns for concatenating strings, the below example adds another column to our table called ProductPlusDescription which concatenates the Product and Description columns to give us a single string with both the Product and Description in.
ALTER TABLE dbo.OrderLines ADD ProductPlusDescription AS Product + ' - ' + Description
To test our new table and calculated columns we first need to add some test data, caution calculated columns can not be included in an insert or an update call, you will get an error if you try to do this.
Here’s our test data:
INSERT INTO dbo.OrderLines (QtyOrdered, UnitCost,Product,Description) VALUES (11, 2.30,'Widget 123','Our red widgets'), (20, 2.5,'Widget 890','Our green widgets');
We can then call a select statement on out table:
SELECT OrderNum, QtyOrdered, UnitCost, ExtendedCost, RetailValue, ProductPlusDescription FROM dbo.OrderLines;
The result from the select statement will be as below: