562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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").|
Thanks to Kevin on my advanced SQL course last week for educating me about these!
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:
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.
You can use the following syntax to create a calculated column:
Here's an example of how to create the products table alluded to above:
-- create table of products
CREATE TABLE tblProduct (
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).
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 ...
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.
25 Aytoun Street