In this article, you’ll study computed columns in SQL Server.
What is a computed column?
A computed column is computed from an associate expression that may use alternative columns within the same table. The expression may be a non-computed column name, constant, function, and any combination of those connected by one or a lot of operators however the subquery can’t be used for the computed column.
Let us understand with an example there is one employee table and we want to calculate the Total_Salary of employees on basis of MEDICAL, HR, DA, PF, and BASIC.
TotalSalary = MEDICAL+HR+DA+PF+BASIC
Computed columns are virtual columns that values are not physically stored in the table unless the column is marked as PERSISTED. Values for computed columns are recalculated whenever they are referenced in a query. Values of the computed columns are updated when any columns value changes.
How to create a computed column in SQL?
CREATE TABLE EMP_SALARY( EMP_ID INT, EMP_NAME NVARCHAR(50), [BASIC] INT, HR INT, DA INT, MEDICAL INT, PF INT, Total_Salary As([BASIC]+HR+DA+MEDICAL+PF) PERSISTED )
Now insert value in EMP_SALARY table.
INSERT INTO EMP_SALARY(EMP_NAME,[BASIC],HR,DA,MEDICAL,PF) SELECT 'Hafeezjaha',15000,1200,1500,700,500 UNION ALL SELECT 'Shaikh',17000,1100,1500,600,400 UNION ALL SELECT 'Anki',19000,1200,1500,400,200 SELECT * FROM EMP_SALARY
In the above image, you can see that we didn’t insert any value in the Total_Salary columns but this column contains values because the Total_Salary column is computed type and calculated the value from values of other columns.
Update the content of the table.
Now we update the value of MEDICAL and DA in the EMP_SALARY table and you can see the changes into values of the Total_Salary column.
UPDATE dbo.EMP_SALARY SET EMP_SALARY.MEDICAL=1000,EMP_SALARY.DA=1600 WHERE EMP_SALARY.BASIC=15000
In the above image, you can see the value of Total_Salary is changed after updating the value of other columns.
Conclusion
Use computed column for a table when you want to insert data into a column after performing the computation on data of another column.
Also check, How To Get All Column Names Of Table In SQL
