Introduction
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 a 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
What are Computed columns in SQL Server?
Computed columns 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 the computed columns 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 a Computed Column in SQL Server?
We can create a computed column by creating a 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; the last Column of this a=table is a type of computing. Now we check the design of the table for the Total_Salary column,
![table]()
If the Persisted property is 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 whenever referenced in a script. If this property is active, the computed Column data will be stored on disk. If the Persisted property is set to on, an index can be created for the computed Column.
We can see that the Total_Salary Column is the computed type, and there is also a formula for this computed Column, and the type of Column persisted.
We can also create a computed column using the Object explorer window. Go to your database, right-click on tables, and select the "New Table" option. Create all required columns and mark any column as computed; select that Column and go to the column Properties window, and write your formula for the computed Column.
![table]()
Now we insert some values into the Employee_Salary table and examine the table's data later.
![table]()
We can see that we did not insert any values for the Total_Salary Column, but this Column contains values because the Total_Salary Column is computed type and calculated from the values of other columns.
Update the content of the table
Now we update the values of the basic and HR column in the Employee_Salary table and examine the changes in values of the 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]()
The above image shows that the value of the Total_Salary columns for Emp_Id 2 and 5 has been changed. So it is clear that if the values of any column are changed, and this Column is part of a computed column, the 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 in the 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.
-
The database engine itself will determine the nullability of a computed column value. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will also produce null results. To overcome this problem, the COLUMN PROPERTY function with the AllowsNull property.
a. A subquery can not be used as an expression for creating a computed column.
b. If we use different data types in our expression, then the operator of lower precedence will try to convert into the higher precedence data type. If the implicit conversion is impossible, an error will be generated.
Conclusion
Use a computed column for a table when you want to insert data into a column after performing the computation on another column's data. You can use a scalar expression or a user-defined function for the computed columns.
Read more articles on SQL Server.