Friday, November 07, 2008

A good reminder - use computed columns in SQL Server 2005 / 2008

I have been doing a lot of work in SQL Server 2005 over the last 6 months and I have found it very helpful to use computed columns to hold any totals or other figures that can be derived from other standard columns within the same table.

As a simple example, in the LaborDetail table I have Regular hours, OverTime hours, and DoubleTime hours. It is very helpful to have the TotalHours column present in the table as well. A computed column provides this number and ensures that it is correct, providing the column's formula is correct.

Computed columns can be added without breaking existing stored procedures or external programs that do INSERTs on the table since computed columns do not need to be included in the field list.

If performance could be considered an issue, you have the option to mark the column attribute "Is Persisted" to true. The column value will be physically carried in the table and will be re-computed only when one of its dependent columns changes. Stored Procedures and external programs are still not affected, but table storage size is increased.

This post might come under the heading of stating the obvious, but I believe it is a good reminder and it certainly has been helpful for me to remember this.

Hope it helps.

Joe Kunk
Okemos, MI

No comments: