Computed column in Sql Server

Have you ever come across any requirement which required saving data in a column that depends on the data of other columns? For example, the total amount of a transaction which would depend on billed amount and the tax amount. But you might need many reports to show the total. There is one way that you would get these two fields in your select query and add them. If you need to store the total on one more column so that you can use it for some other purpose, you could add that on your store proc. Another way would be by using trigger. But I found Computed columns more efficient and decent approach if we don’t have much logic involved storing the calculated data.

Let’s look at how to create these columns.

CREATE TABLE [dbo].[Billing](

[BillID] [int] NOT NULL,
[CustomerName] [varchar](50) NOT NULL,
[City] [varchar](50) NULL,
[BillAmount] [float] NULL,
[Tax] AS (([BillAmount]*(10))/(100)),
[TotalAmount] AS ([BillAmount]+([BillAmount]*(10))/(100)))

If you look at above table, the columns Tax and TotalAmount do not have any datatype assosiated but it depends on BillAmount field.

You can also create this by using SQL table editor as shown below

Now lets insert some data to our table and see the values


insert into Billing values (1,'ABC','Blr',100)
insert into Billing values (2,'XYZ','NY',200)



The data is inserted and now lets look at our table to see how is our computed columns.

Here we go

 

The columns has the data what we had expected.




What happens if you try to insert data tp computed columns? Let’s try that

insert into Billing (BillID,CustomerName,City,BillAmount,Tax,TotalAmount) values (3,'XYZ','NY',200,10,110)


Msg 271, Level 16, State 1, Line 1
The column "Tax" cannot be modified because it is either a computed column or is the result of a UNION operator.
Msg 271, Level 16, State 1, Line 1
The column "TotalAmount" cannot be modified because it is either a computed column or is the result of a UNION operator.



Error!!!! Yes you need to remember that you cannot insert or update computed column data.



We can also create index on these columns. To create index on these column, you need to specify the column as PERSISTED. For more details on this http://msdn.microsoft.com/en-us/library/ms191250.aspx



We can also add the case statements on the computed columns. Let’s try that


CREATE TABLE MyTable (

number1 INT,
number2 INT,
operatoration CHAR,
total AS CASE operatoration
WHEN '+' THEN number1+number2
WHEN '-' THEN number1-number2
ELSE number1*number2
END

) ;



I am going to insert few records to the table

insert into MyTable values(10,10,'+')
insert into MyTable values(20,10,'-')
insert into MyTable values(20,10,'')



Look at the result now

 Cool isn’t it?




I found one more interesting article on computed columns. http://www.mssqltips.com/tip.asp?tip=1682 Take a look at it.