Blog ala Vidar

You might have heard the expression “computed column” before. If not, it’s an expression used for columns that are computed 🙂 A little more detailed, Field3 may be Field1 + Field2, Field1 * Field2 or similar. This is actually a pretty neat feature in SQL Server. Why? Let’s take a transactions table in an accounting system as an example. We’ve got a field called CurrencyAmount and one field called ExchangeRate. In most cases you want to calculate these as your currency’s amount. Therefore you make a field called Amount, and sets it as a computed column with the following formula: CurrencyAmount * ExchangeRate.

So, in your views, you can just use:

SELECT Amount FROM Transactions

Instead of:

SELECT CurrencyAmount * ExchangeRate AS Amount FROM Transactions

Also, you will always get the correct amount. In some cases I’ve seen people who have made a column “Amount” which was not computed. Then the user has to insert values into all three fields, and this might cause issues. Some users only use the “amount” field, others only the CurrencyAmount etc.

But what about persisted vs non-persisted? Persisted means that it’s stored WHEN SAVING the record. This means that every time you select/filter on the column, it’s picking data from the hard drives. If you’ve got heavy calculations this might be the best thing, but in many cases non-persisted is the best thing. Non-persisted means that it’s calculating Currency * ExchangeRate for each record when you SELECT the record(s). You might think that this is slower, but with the CPUs nowadays, this isn’t a problem at all. The bottleneck for SQL Server is disks, or IO-activity. You (almost) always want to reduce the amount of IO in a query, so next time you make a computed column, take a minute to think about if you want it persisted or not 🙂

And, just for the record. You can only have persisted computed columns in tables. Computed columns in views will always be non-persisted.