Today I wanted to brainstorm on some of the uses for computed columns in SQL Server. When the computed column is created it can either be persisted or dynamic. The kind you use is determined by the purpose of the computed column.
Sometimes we like to track the birth date of a person, or some significant date. Then we want to know how many years have passed since that significant date. In the case of a person, this would compute their age. You have many options to calculate age through functions when querying the data, through consuming software business rules, etc. One option is to have a computed column in the table for age that evaluates based on the birth date contained in that record.
When calculating age, you can’t have a persisted value because the age is not a static fact. Each year it the persons age increments. By not persisting the computed value you don’t have to build a function or do math outside of the table…the calculation is embedded in the table definition itself.
Why would you want to persist a computed column? The primary reason is performance. For static calculated facts you can choose to persist them because they do not change. The formula is still embedded and enforced through the table definition. Because it is physically persisted like any other column, this column may be included in an index due to its static nature. What you have gained here is the embedding of your calculation logic in the table so it is always consistent throughout.
Are you using computed columns? What do you use them for? Are there any tips you would like to share with others regarding using them? Share your thoughts here or by email to btaylor@sswug.org.
Cheers,
Ben