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:
| 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.
