Rudy Rodarte . com

Information

This article was written on 12 Mar 2010, and is filled under SQL Blogs.

Current post is tagged

, ,

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.

CREATE TABLE dbo.DateDiffTable
	(
	RowID int NOT NULL IDENTITY (1, 1),
	DateStart smalldatetime NOT NULL,
	DateFinish smalldatetime NOT NULL,
	DateDiffs  AS datediff(DD,DateStart,DateFinish)
 NOT NULL	)
GO

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.

CREATE TABLE dbo.DateDiffTable
	(
	RowID int NOT NULL IDENTITY (1, 1),
	DateStart smalldatetime NOT NULL,
	DateFinish smalldatetime NOT NULL,
	DateDiffs  AS datediff(DD,DateStart,DateFinish) PERSISTED NOT NULL
     )
GO

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

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