# Basic Computed Columns

, 2011-08-16

I ran into an issue recently with a computed column, which I’ve rarely used, so I investigated them and wrote this short piece in computed columns, mostly as a reminder for me.

### What’s a computed column?

You can read the BOL definition, but basically it is a column whose value is based on the values of other columns in a table. By having SQL Server perform the calculation, you prevent errors in the logic, or more likely, get around the chance that some application might forget to perform an update.

Let’s say I have this simple orderdetail table.

```CREATE TABLE OrderDetail
( OrderID INT
, ProductID INT
, Qty INT
, Price NUMERIC(10, 2)
, LineTotal NUMERIC(10, 2)
)
```

If I want to add a simple order to this table, I can do this:

```INSERT dbo.OrderDetail
( OrderID ,
ProductID ,
Qty ,
Price ,
LineTotal
)
VALUES  ( 1 , -- OrderID - int
23 , -- ProductID - int
10 , -- Qty - int
8.50 , -- Price - numeric
85.00  -- LineTotal - numeric
)
```

Note that in this case I have to perform the arithmetic of having the line total equal to the price multiplied by the quantity. The math is:

Linetotal = Qty * Price

85.00 = 10 * 8.50

However if I were to make a mistake in the arithmetic and insert \$84 instead, or even \$8.50, the SQL Server would not catch this.

Instead, I could use a trigger to calculate this value, but triggers seem to have other overhead and force me to maintain them. Instead I could use a computed column, I can save space by not persisting these columns, or I can persist them and index them if needed.

In my simple example, I could change my table to be this:

```DROP TABLE dbo.OrderDetail
go
CREATE TABLE OrderDetail
( OrderID INT
, ProductID INT
, Qty INT
, Price NUMERIC(10, 2)
, LineTotal AS (Qty * Price)
)
GO
INSERT dbo.OrderDetail
( OrderID ,
ProductID ,
Qty ,
Price
)
VALUES  ( 1 , -- OrderID - int
23 , -- ProductID - int
10 , -- Qty - int
8.50 -- Price - numeric
)
go```

The LineTotal value is not stored in the table, which isn’t a big issue in this case. In fact, it means that I am saving a few bytes per row, which could translate into quite a few rows for this table (meaning less pages, less I/O, more chance of this remaining in the buffer pool, etc). The tradeoff is the computation is performed for this result set for every query. Which is better? It depends

Notice that I don’t have a value for my computed column in the insert statement, but the results from this version are the same as the previous one:

In fact, if I try the first insert above, which has the LineTotal in it, I get this error:

Msg 271, Level 16, State 1, Line 1

The column "LineTotal" cannot be modified because it is either a computed column or is the result of a UNION operator.

That’s a simple look at computed columns. Are they useful? I’ve never really worried about them, allowing either a stored procedure or the application to handle any logic like this. In general I dislike storing computations, even defined ones like this, but that’s me.

Use them if they fit your environment.

Filed under: Blog Tagged: sql server, syndicated, T-SQL

# Book Review: Big Red - Voyage of a Trident Submarine

I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...

2009-03-10

# Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

# Inserting Markup into a String with SQL

In which Phil illustrates an old trick using STUFF to intert a number of substrings from a table into a string, and explains why the technique might speed up your code...

2009-02-18

# Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17