Computed Columns In SQL Server

In this article, you will learn about Computed Columns in SQL Server.

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 in SQL Server

 
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
  1. /*Select Values From Table*/  
  2.   
  3. SELECT * FROM dbo.Employee_Salaryes  
  4.   
  5. /*Update Record*/  
  6.   
  7. UPDATE dbo.Employee_Salary  
  8.   
  9. SET Employee_Salary.Medical=1000,Employee_Salary.HR=1500  
  10.   
  11. WHERE  
  12.   
  13. Employee_Salary.Basic=17000  
  14.   
  15. /*Select Values From Table */  
  16.   
  17. 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
  1. ALTER TABLE dbo.Employee_Salary  
  2. ADD Total_Salary AS ([Basic]+([HR]*2)+([Da]*1.5)+([Medical]*1.4)+[Pf]+[Esi])  
To change Any Existing Column
 
Syntax
  1. Alter Table Table_Name Drop Column Column_Name  
  2. ALTER TABLE Table_Name ADD Column_Name AS (Write_Formula)  
Example
  1. ALTER TABLE dbo.Employee_Salary DROP COLUMN dbo.Employee_Salary.Total_Salary  
  2. ALTER TABLE dbo.Employee_Salary  
  3. 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
  1. CREATE FUNCTION Calculate_Salary(@Employee_Typeint,@basic int,@Hrint,@Da int, @Medical [int],@Pf int,@Esi [int])  
  2.   
  3. RETURNS [float]  
  4.   
  5. WITH SCHEMABINDING  
  6.   
  7. AS  
  8.   
  9. BEGIN  
  10.   
  11. DECLARE @Total_Salary [float];  
  12.   
  13. IF @Employee_Type=1  
  14.   
  15. BEGIN  
  16.   
  17. SET @Total_Salary= @basic+@Da*1.2+@Esi*1.4+@Hr*2+@Medical*1.8+@Pf*2.5  
  18.   
  19. END  
  20.   
  21. ELSE IF @Employee_Type=2  
  22.   
  23. BEGIN  
  24.   
  25. SET @Total_Salary= @basic+@Da*1.3+@Esi*1.5+@Hr*2+@Medical*1.8+@Pf*2.5  
  26.   
  27. END  
  28.   
  29. IF @Employee_Type=3  
  30.   
  31. BEGIN  
  32.   
  33. SET @Total_Salary= @basic+@Da*1.8+@Esi*1.6+@Hr*3+@Medical*1.8+@Pf*2.5  
  34.   
  35. END  
  36.   
  37. RETURN @Total_Salary;  
  38.   
  39. 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
.
Read more articles on SQL Server