BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
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).
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.