12,759,875 members (31,392 online)
Technical Blog
alternative version

#### Stats

10.9K views
6 bookmarked
Posted 3 Jan 2012

# How to Use Computed or Calculated Columns

, 3 Jan 2012 CPOL
 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 lets 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 lets test out our fields, using nothing more than a `SELECT`.

`SELECT * FROM Programmers`

We will get the following results:

ProgrammerIDFirstNameLastNameMiddleInitDateOfBirthFullNameAge
1DavidAndrewsC1984-09-20 00:00:00.000Andrews, David C25
2BillyJenkinsNULL1990-01-20 00:00:00.000Jenkins, Billy19
3RobertAndersonKNULLAnderson, Robert KNULL

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 8:53 Dave C Andrews 3-Jan-12 8:53
 Last Visit: 31-Dec-99 19:00     Last Update: 25-Feb-17 9:28 Refresh 1