Monday, April 06, 2009

Computed Columns in SQL Server 2005

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

For example, in the AdventureWorks sample database, the TotalDue column of the Sales.SalesOrderHeader table has the definition: TotalDue AS Subtotal + TaxAmt + Freight.

Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it. For more information, see Creating Indexes on Computed Columns.


Example:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Test](
[id] [int] NULL,
[Name] [varchar](max) NULL,
[CSVName] AS (checksum([Name])) PERSISTED
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


the Checksum value for Name will be stored in the CSVName column and it will be updated every time there is a change in Name column.


Reference :
http://msdn.microsoft.com/en-us/library/ms191250(SQL.90).aspx

No comments: