Computed Columns In SQL Server

Introduction

This article will help the user to write a complex calculation in the back-end (SQL Server). A computed column is computed from an expression that can use other columns in the same table.

I have created ‘Inventory’ table which contains ‘ItemsInStore and ItemsInWarehouse’ columns and calculates the Sum total of these columns for each row.

Create Table

CREATE TABLE Inventory(ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL)

Alter Table

Now, add a computed column that returns Sum of the columns.

ALTER TABLE Inventory
ADD TotalItems AS ItemsInStore + ItemsInWarehouse

In the above image, we created computed column i.e. ‘TotalItems’. It will store the sum of the column.

Insert Data

Insert into Inventory(ItemID,ItemsInStore,ItemsInWarehouse) values(1,2,3)
Insert into Inventory(ItemID,ItemsInStore,ItemsInWarehouse) values(2,3,3)
Insert into Inventory(ItemID,ItemsInStore,ItemsInWarehouse) values(3,10,5)

In the preceding INSERT query, I have not supplied a value for the TotalItems column because it is a computed column that is automatically generated by the values when the data is stored.

Result