Computed Columns in SQL Server

Today I will talk about one of the very interesting feature called "Computed Columns" in SQL Server. This is a column in table but you will not assign any specific data type to this as you do it in regular CREATE TABLE statement.

You can ask this question, if there is no data type associated with this column then what kind of data i can store? And how to insert the data into this column?

Before I answer these questions, let's look at the basic syntax of table creation.

Let's take an example; I want to create a table to store Employee Working hours and Monthly Rate.

CREATE TABLE EMPLOYEE_WORKING_HRS(EMP_ID INT, ENAME VARCHAR(30), YR INT, MON VARCHAR(3), WORKING_HRS INT, RATE INT);



Let’s insert few records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES
(100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES
(102,'NAT',2009,'JAN',176,120);INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);





Now if you want to find out what is the salary of each of these employees then you need to multiply WORKING_HRS with RATE.

select EWR.*, EWR.WORKING_HRS*EWR.RATE AS SALARY from EMPLOYEE_WORKING_HRS EWR



When ever we want to find out the salary then we have to multiply RATE column with WORKING_HRS. It’s an extra overhead on the developer to have this conversion. Instead you can have this column in table itself but its violation to the normal forms.

So now you are in mixed state, you don’t want to store this information in the table and you don’t want to have this Multiplication in the query each time. The solution for this is “Computed Columns”.

CREATE TABLE EMPLOYEE_WORKING_HRS
(EMP_ID INT,
ENAME VARCHAR(30),
YR INT,
MON VARCHAR(3),
WORKING_HRS INT,
RATE INT,
SALARY as WORKING_HRS*RATE);

Created a column SALARY and the definition of the column is WORKING_HRS*RATE.

Let’s insert same old records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES (100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);


Now Lets query the table.



The fundamental principle behind this is when the data is returned; the computation is applied to return the result.

However, you can force a computed column to physically store data by using PERSISTED keyword. With this keyword, this will be just like any other column and gets modified whenever there is an UPDATE/INSERT on the underlying columns.