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

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

SQL Server

Alter Table

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

  1. ALTER TABLE Inventory  
  2. ADD TotalItems AS ItemsInStore + ItemsInWarehouse 

SQL Server

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

Insert Data

  1. Insert into Inventory(ItemID,ItemsInStore,ItemsInWarehouse) values(1,2,3)   
  2. Insert into Inventory(ItemID,ItemsInStore,ItemsInWarehouse) values(2,3,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

SQL Server