Computed Column in SQL

The computed columns in SQL is a concept of filling calculated data in a column. Suppose have a table in which the first name and the last name columns exist but you need a column where you need both first name and last name in a single column. You can create one such column by using the computed column feature. In this type of column, you don’t need to fill in the data. The data will be populated automatically based on the other columns in the same table.

How to create a Calculated Column

There are two ways of creating computed colums in SQL.

Using GUI Mode
By writing SQL query

Let me tell you GUI mode for now. But we will be creating the database and table using SQL queries. Let’s begin.

For your convenience, I am going to provide you the queries for creating a database, table, and inserting data inside the table.

Below are the queries that you need to run in the same order that they are given to create the database and table to work on. Once the database and the table is created and fill all the data inside the table. You need to follow the instructions to create a computed column.

Creating Database

create database Computed_Column_TestDB;

Select the database

use Computed_Column_TestDB;

Create a table

create table Computed_Column_TestTable
(
UserID int Primary Key identity,
First_Name varchar(20),
Last_Name varchar(50),
Data_of_Birth Date
);

Fill the data inside the table

insert into Computed_Column_TestTable values
(
'Maria', 'Joseph', '1998-05-11'
),
(
'Jenice', 'Carey', '1991-07-22'
),
(
'John', 'Smith', '1985-08-13'
),
(
'Barbara', 'Morey', '1993-08-29'
),
(
'Jonny', 'Depp', '1982-05-18'
),
(
'Violeta', 'Mudd', '1962-01-12'
),
(
'Bebe', 'Temme', '1985-05-18'
),
(
'Lavern', 'Martin', '1998-07-22'
),
(
'Barbara', 'Doremon', '1992-07-22'
),
(
'Jonny', 'Wolen', '1972-05-18'
),
(
'Zoraida', 'Smith', '1982-07-22'
),
(
'Verda', 'Albert', '1982-05-18'
);

See the data inside the table

Select * from Computed_Column_TestTable;

At this point, you have for all the database and the table to work on. You have even the data filled inside it. You can start following the steps to create a computed column.

Let’s create a computed column

Step 1

Right click on the table inside the database that you have created. Then click on design.

Step 2

As soon as you click on design option, the following design will be on screen. This is the design that you have created by runnig the above create table query.

Step 3

Below the date of the birth column, you need to create a new column with Full_Name and set the data type as varchar(50). The number 50 is the number of characters that can be saved inside this column.

Step 4

You need to click on the play-like button just before the Full_Name column and you will see the options related to that column below the design of the table. Choose the Formula option under the Computed Column Specification option and type the formula which is First_Name + ‘ ‘ + Last_Name.

Note – Do not forget to save the table by pressing the save button in the toolbars. In case the table is not being saved. You need to follow the below steps.

Click on Tools in the Menu bar > Options > Designers > Uncheck prevent saving changes that require table re-creation > Press Okay

Now you will be able to save the changes that you have made in table design.

Step 5 – Check the table is populated with the full names or not

Run the following command again.

Select * from Computed_Column_TestTable;

The above output you will see. You can see a new column has been created which has both first names and last names inside it.

I hope the article was helpful. This feature of SQL is very useful and comes in use very often in the big IT organizations like Google, Microsoft, IBM. In case you have any confusion or question related to the article, feel free to comment. I would be glad to help.

Take Care Guys!

Learn about Views in SQL.

Computed Column in SQL