Click here to Skip to main content
13,405,603 members (55,079 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


6 bookmarked
Posted 3 Jan 2012

How to Use Computed or Calculated Columns

, 3 Jan 2012
Rate this:
Please Sign up or sign in to vote.
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 NULLs 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:

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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Dave C Andrews
Software Developer (Senior) LBMC Technologies
United States 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...


Comments and Discussions

QuestionAges Pin
Dave C Andrews3-Jan-12 8:53
memberDave C Andrews3-Jan-12 8:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180221.1 | Last Updated 3 Jan 2012
Article Copyright 2012 by Dave C Andrews
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid