Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

How to Use Computed or Calculated Columns

, 3 Jan 2012 CPOL
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 lets 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 lets 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.

License

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

Share

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.
Follow on   Twitter

Comments and Discussions

 
QuestionAges PinmemberDave C Andrews3-Jan-12 8:53 

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

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

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