This page has 0 threads | Add post

Wise Owl guides and tutorials

Computed Columns in SQL Server

Written by Andy Brown

In this tutorial

What is a Computed Column?
Creating a Computed Column in SQL
Creating Calculated Columns in Table Definitions
Persisting Computed Columns

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:

SELECT

PersonId,

FirstName,

LastName,

FirstName + ' ' + LastName AS 'Full name'

FROM

tblPerson

ORDER BY

PersonId

This would give you something like this:

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:

SELECT

Product,

Net,

VatRate,

Net * (1+VatRate) AS Gross

FROM

tblProduct

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

) VALUES (

'Fluffy owls', 9.99, 0.175

)

-- show all the rows

SELECT * FROM tblProduct

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

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:

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:

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:

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:


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.

Previous
tutorial

WHERE criteria (version B)

Previous
part

Dealing with nulls - ISNULL, COALESCE and CASE

Top of page

Next
part

Next
tutorial

Dates and times

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Calculations and Expressions

Computed Columns in SQL Server

What is a Computed Column?

Creating a Computed Column in SQL

Creating Calculated Columns in Table Definitions

Persisting Computed Columns

Head office

London

Manchester