Creating computed columns in SQL tables
This short blog explains how you can create calculated formulae for fields in an SQL table (known as "computed columns").

This blog is part of our much longer online training series on SQL (or have a look at our courses in SQL).

Posted by Andy Brown on 10 December 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Computed Columns in SQL Server

Thanks to Kevin on my advanced SQL course last week for educating me about these!

What is a Computed Column?

There are some tables in SQL Server which you'll always do the same calculations on.  Here are a couple of examples.

If you have a first name and a last name, you're going to want to create the full name over and over again:





FirstName + ' ' + LastName AS 'Full name'





This would give you something like this:

Full name derived column

The full name is the first name, plus a space, plus the last name.


Calculating the gross amount based on the net amount and VAT rate is another example:





Net * (1+VatRate) AS Gross



To avoid having to keep typing in the same formulae over and over again, you could embed it ina a user-defined function or a view; but you could also include it within the table as a computed column.

Creating a Computed Column in SQL

You can use the following syntax to create a calculated column:

FieldName AS (Formula)

Here's an example of how to create the products table alluded to above:

-- create table of products

CREATE TABLE tblProduct (

Product varchar(100),

Net decimal(5,2),

VatRate decimal(5,2),

Gross AS (Net * (1+VatRate))


-- add a new product

INSERT INTO tblProduct (

Product, Net, VatRate


'Fluffy owls', 9.99, 0.175


-- show all the rows

SELECT * FROM tblProduct

If you ran this SQL, this is what you'd get:

New row in product table

The gross is automatically calculated.


Note, however, that you can't use an UPDATE or INSERT statement to change the value of a calculated column (nor should you expect to be able to do this).

Creating Calculated Columns in Table Definitions

You could also create the Gross column shown above in a table's design view:

Creating a calculated column

Follow these steps:


When you save, close and re-open the table, you'll see a couple of changes.  Firstly, any data type has vanished:

Data type vanished for computed column

You can't specify a data type for a computed column - SQL will work this out itself.


Secondly, the bracket monster has attacked your formula:

Formula with lots of brackets

SQL Server has added the odd parenthesis ...


Persisting Computed Columns

What does the Is Persisted property mean in the diagrams above?  The answer is that a persisted column will be saved with the columns upon which it is based.  So here's what SQL will save for our persons table:

Columns without persistence Columns with persistence
Without persistence With persistence

Thus if you're going to be inserting or updating data a lot but not then querying it much, don't persist (the default); but if you'll hardly ever change data, but frrequently query it, set Is Persisted to Yes.

This blog has 0 threads Add post