Storing Computed Columns in SQL Server

From time to time you will run into a case where you need to store computed columns in your SQL Server tables. Examples of computed columns include taxes on a sale or business days between several dates. Setting  up computed columns in SQL Server 2008 is fairly simple. You simply give the column a name and then specify your calculation instead of giving a datatype. You could even use a case statement instead of functions and calculations. Here is an example of a table that calculates days between 2 dates.

The sample table has an ID, 2 dates and the difference between those two dates. The trouble is, every time you run a select against this table, you are executing a datediff against the two date columns. If only we could calculate the difference once, and then persist that number in the table. You can in SQL Server 2005 and higher. What you need is to declare the column as PERSISTED.

When your column is Persisted, the value is calculated once, and only reevaluated on insert and update statements. I have always heard that you want to avoid storing computed columns in your table and let the calculation happen on the client/front end or as part of your stored procedure. However, there are cases where storing computed data makes sense. Just remember, the data must be dynamic and the calculation must be deterministic.


Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter