13,000,982 members (61,786 online)
Technical Blog
alternative version

#### Stats

11.1K views
6 bookmarked
Posted 3 Jan 2012

# How to Use Computed or Calculated Columns

, 3 Jan 2012
 Rate this:
You can have your tables make computations themselves, without running through a view.

You are probably used to writing views to access data in your tables when there is some sort of computation that must be made in a field. But did you know that you can have your tables make computations themselves, without running through a view?

This can be done with Computed (or Calculated) Columns. These columns are table-level expressions that can operate on the other fields in a given record.

Let’s create a table which uses computed columns. I am going to create a table called `Programmers` which allows me to store a programmer’s first name, last name, middle initial, and date of birth. The table will include two computed columns: one which combines the elements of the name into a `FullName` field, and a second column which tells me the programmer’s age. This will all be achieved directly in the table, without the use of a view.

First, let’s create the table. Here is the query to create all of the fields except the computed ones.

```CREATE TABLE Programmers
(
ProgrammerID INT IDENTITY(1,1) NOT NULL,
FirstName NVARCHAR(30),
LastName NVARCHAR(30),
MiddleInit NCHAR(1),
DateOfBirth DATETIME,```

Now, let’s create our first computed column. The syntax is simple. Just begin with the name of the column, and then in parenthesis, define the expression which will calculate the value of the column.

Let’s begin with the `FullName` calculation. Just add the `LastName`, a comma, `FirstName`, and `MiddleInit`, and then trim white space off the right to handle a missing initial.

```FullName AS (rtrim(coalesce(LastName, '') + ', ' +
coalesce(FirstName, '') + ' ' +
coalesce(MiddleInit, ''))),
```

There we have it, our `FullName` calculation. Each field is encapsulated in coalesce to handle `NULL` values properly.

The last calculation will be the `age`. The `age` is simply the difference in years of the current date from the birth date.

```    Age AS (datediff(year, DateOfBirth, getdate()))
)```

We also add a following parenthesis to close out our “`CREATE TABLE`” statement.

Now let's insert some test data. I added a few records with some `NULL`s for good testing measure. All standard stuff here. Notice we are not inserting the `FullName` or `Age` values.

```INSERT INTO Programmers(FirstName, LastName, MiddleInit, DateOfBirth)
VALUES ('David', 'Andrews', 'C', '1984-09-20')
INSERT INTO Programmers(FirstName, LastName, MiddleInit, DateOfBirth)
VALUES ('Billy', 'Jenkins', NULL, '1990-01-20')
INSERT INTO Programmers(FirstName, LastName, MiddleInit, DateOfBirth)
VALUES ('Robert', 'Anderson', 'K', NULL)```

Now let's test out our fields, using nothing more than a `SELECT`.

`SELECT * FROM Programmers`

We will get the following results:

 ProgrammerID FirstName LastName MiddleInit DateOfBirth FullName Age `1` `David` `Andrews` `C` `1984-09-20 00:00:00.000` `Andrews, David C` `25` `2` `Billy` `Jenkins` `NULL` `1990-01-20 00:00:00.000` `Jenkins, Billy` `19` `3` `Robert` `Anderson` `K` `NULL` `Anderson, Robert K` `NULL`

I emphasized the calculated fields above. Our query did not calculate them, they were considered to be just a part of the table since they are calculated fields.

One thing to keep in mind about calculated fields is that they are difficult to modify. You have to `DROP` the field and then `ADD` it back with the same name. This can change the order of fields in your query if you use `SELECT *`. It can also affect any triggers you may have which rely on the fields being in a certain order.

Also keep in mind any overhead that calculated fields may produce. It’s a good idea to use them for absolutely basic, atomic information, such as what I presented above. Complex calculations could become taxing to your queries.

## Share

 Software Developer (Senior) LBMC Technologies United States
I've been developing software since I was 9 years old, in BASIC on a Precomputer 2000. OK, that's a stretch, but hopefully some of the stuff that I learned from that little toy have helped my professional career.

I have a bachelors degree in computer science from MTSU. Currently, I am employed as a senior software developer for LBMC in Nashville, TN. We serve as consultant developers for clients across the southeastern United States, developing mostly in Microsoft .Net with Microsoft SQL Server.

I greatly enjoy my job and my hobbies lie in open source software and in teaching others how to be better programmers. I have a beautiful wife, 2 dogs (a dachshund and an olde-english bulldog) and a cat.

## You may also be interested in...

 Pro Pro

 First Prev Next
 Ages Dave C Andrews3-Jan-12 7:53 Dave C Andrews 3-Jan-12 7:53
 Last Visit: 31-Dec-99 18:00     Last Update: 26-Jun-17 0:45 Refresh 1

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Web02 | 2.8.170624.1 | Last Updated 3 Jan 2012
Article Copyright 2012 by Dave C Andrews