This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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:

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR