Computed Columns In SQL

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

Submit a Comment

Your email address will not be published.

Subscribe

Select Categories