Computed Columns In SQL Server

Article

In this article you will learn about Computed Columns in SQL Server. A computed column is computed from an expression that can use other columns in the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators but the subquery can’t be used for computed column.

For example, Employee_Salary table contain Emp_Id,Basic, HR, Da, Medical, Pf,+ Esi and Total_Salary column and Total_Salary column is computed type so, formula for Total_Salary is:

Total_Salary=Basic+HR+Da+Medical+Pf+Esi

Computed columns actually are virtual columns that are not physically stored in the table unless the column is marked PERSISTED. Values for computed columns are recalculated every time they are referenced in a query. Values of computed column are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, we can create an index on a computed column. Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

How to Create Computed Column

We can create a computed column either in create query or using the Object Explorer, here we read about both methods.

Using Create Command

create

In the above image we create an Employee_Salary table and last column of this a=table is type of computed. Now we check the design of table for Total_Salary column,

table

If Persisted property is set to off then the calculated column will be just a virtual column. No data for this column will be stored on disk and values will be calculated every time when referenced in a script. If this property is set active then data of computed column will be stored on disk. If Persisted property is set to on, then index can be created for computed column.

We can see that Total_Salary column is computed type and there is also a formula for this computed column and type of column is persisted.

We can also create a computed column using the Object explorer window. Go to your database, right click on tables, select “New Table” option. Create all columns that you require and to mark any column as computed, select that column and go to column Properties window and write your formula for computed column.

table

Now we insert some value into Employee_Salary table and examine the data of table later.

table

We can see that we did not insert any values for Total_Salary column but this column contain values, because Total_Salary column is computed type and calculated the value from values of other columns.

Update the content of table:

Now we update the values of basic and HR column and in Employee_Salary table and examine the changes into values of Total_Salary column.

Query:

/*Select Values From Table*/
SELECT * FROM dbo.Employee_Salaryes
/*Update Record*/
UPDATE dbo.Employee_Salary
SET Employee_Salary.Medical=1000,Employee_Salary.HR=1500
WHERE
Employee_Salary.Basic=17000
/*Select Values From Table */
SELECT * FROM dbo.Employee_Salaryes

Output:

output

In the above image we can see that value of Total_Salary columns for Emp_Id 2 and 5 has been changed. So it is clear that if values of any column are changed and this column is part of a computed column then values of a computed column will also change.

Add computed Column to an existing table:

Syntax:

ALTER TABLE Table_Name ADD Column_Name AS (Write_Formula)

Example:

ALTER TABLE dbo.Employee_Salary
ADD Total_Salary AS ([Basic]+([HR]*2)+([Da]*1.5)+([Medical]*1.4)+[Pf]+[Esi])

To change Any Existing Column:

Syntax:

Alter Table Table_Name Drop Column Column_Name
ALTER TABLE Table_Name ADD Column_Name AS (Write_Formula)

Example:

ALTER TABLE dbo.Employee_Salary DROP COLUMN dbo.Employee_Salary.Total_Salary
ALTER TABLE dbo.Employee_Salary
ADD Total_Salary AS ([Basic]+([HR]*2)+([Da]*1.5)+([Medical]*1.4)+[Pf]+[Esi])

Use User_Define Function Into Computed Column:

Now we learn how to use user defined functions for computed columns. First we create a function that returns the computed salary

Function:

CREATE FUNCTION Calculate_Salary(@Employee_Typeint,@basic int,@Hrint,@Da int, @Medical [int],@Pf int,@Esi [int])
RETURNS [float]
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Total_Salary [float];
IF @Employee_Type=1
BEGIN
SET @Total_Salary= @basic+@Da*1.2+@Esi*1.4+@Hr*2+@Medical*1.8+@Pf*2.5
END
ELSE IF @Employee_Type=2
BEGIN
SET @Total_Salary= @basic+@Da*1.3+@Esi*1.5+@Hr*2+@Medical*1.8+@Pf*2.5
END
IF @Employee_Type=3
BEGIN
SET @Total_Salary= @basic+@Da*1.8+@Esi*1.6+@Hr*3+@Medical*1.8+@Pf*2.5
END
RETURN @Total_Salary;
END

Now we use this function into computed column.

function

Limitations of Computed Column:

A computed column cannot be the target of an INSERT or UPDATE statement.
We can’t reference columns from other tables for a computed column expression directly.
Nullability for a computed column value will be determined by the database engine itself. The result of most expressions is considered nullable even if only non-nullable columns are present, because possible underflows or overflows will produce null results as well. To overcome this problem the COLUMNPROPERTY function with the AllowsNull property.

a. A subquery can not be used as an expression for creating a computed column.

b. If we are using different data types in our expression then operator of lower precedence will try to convert into the higher precedence data type. If implicit conversion is not possible then error will be generated.

Conclusion:

Use computed column for table when you want to insert data into a column after performing the computation on data of another column. You can use a scalar expression or a user defined function for the computed columns.