What is a Computed Column

A computed column is a column that users will not enter data for, but the SQL Server will compute and store a value in. The expression for a computed column may include the names of other columns in the table. These columns can also be combined with literal values and operators.

For example, if we had an orders table that contained the columns, quantity and unit cost, we could define a column called cost that we could define as quantity * unitCost. If we wanted to store the tax due, we could define that column as (quantity * unitCost) * .075. The tax in my area is currently 7.5%.

Usually the values generated by a computed column are not stored in the database. Instead the column is virtual. BUT, these values can be stored. If the value created by the computation is deterministic… then you can store the data.

A deterministic value is one that is always the same, given the same inputs. ColumnA + ColumnB would be deterministic, but getDate() would not.

My one word of caution at this point is beware how much you compute and store in the database. You should always weigh the costs of storing these values, versus computing them as you need them. If the compute time is negligable… just compute it in your view, or one demand. If the compute time takes a while, such as the case when you do some nasty text manipulations… then definitely pre-compute those and store them in a computed column!

As usual, using this will be determined by your circumstances.

Adding a Computed Column

You can either add a computed column at the time you define the table, or you can add it after the fact.

Adding a Computed Column During Table Creation

CREATE TABLE BatSupplies (
	  itemID INT IDENTITY(1,1)
	, name VARCHAR(255) NOT NULL
	, batifiedName AS 'BAT' + name
)

In our example we built a table that has and ID and a name. Then, keeping with the 60’s style batman, everything must be called BAT-something… so our table takes whatever name is stored and puts BAT at the beginning.

Like I was mentioning before, if this computation were intense (either in CPU or time) you could actually store the data, rather than making the column virtual. If you wanted to do that, you’d have to alter your previous statement by adding PERSISTED.

CREATE TABLE BatSupplies (
	  itemID INT IDENTITY(1,1)
	, name VARCHAR(255) NOT NULL
	, batifiedName AS 'BAT' + name PERSISTED
)

Adding a Computed Column After the Table Has Been Created

Just like any change to a table, you’ll need to run the ALTER TABLE statement. The only differece is instead of passing a datatype for the new column, you’ll pass the formula.

ALTER TABLE BatSupplies ADD batifiedName AS 'BAT' + name

And if you want the column to Persist:

ALTER TABLE BatSupplies ADD batifiedName AS 'BAT' + name PERSISTED

That’s it for adding a computed column. You’ll need to consider this versus a view when you go about implementing business logic in your database. Both are valid under the right circumstances. As a developer you will want the fastest response times you can get. If you ever have the issue where your first choice is slow, simply test the other solution.

That test alone could save you a lot of grief, especially if you’re ever questioned on your logic.

That’s it for this time. If you have any questions, send them in!