Computed Vs Persisted Column

Computed Columns

Tables in SQL Server 2005 can also have computed columns. A computed column is an expression that defines the value of the column. By default, data is not contained within the column, but instead it is a virtual column.

As an example, a parts table could have an InventoryCost column created as a computed column. The expression would be QuantityOnHand * Cost (assuming we have both these columns in the table).

Be aware that the computed columns can be included in SELECT lists, WHERE clauses, ORDER BY clauses, and more. Computed columns can’t have data inserted or updated via INSERT or UPDATE statements.

The following script would create the InventoryCost computed column within a table. The bolded line shows the computed column.

USE Chapter2; GO CREATE TABLE ComputedColumnTest (       QuantityOnHand int NULL,         Cost money NULL,         InventoryCost AS (QuantityOnHand * Cost) );

Persisted Column

A persisted column is a computed column that actually stores the computed data within the table, instead of computing the value of the data each time it is queried.

When data is inserted or updated into a table with a persisted computed column, SQL Server generates the value by using the formula of the computed column and storing the result with the rest of the table.

When should you use a persisted computed column over a nonpersisted computed column? Generally, simple operations or computations are quicker as nonpersisted computed columns. This is because processors and memory are quicker than disk I/O. For complex operations, a persisted computed column may be quicker. Additionally, a special case exists where you can use a persisted computed column to create an index. We’ll explain this later in the section.

The only change to the script is to add the word PERSISTED to the end of the table definition.

USE Chapter2; GO CREATE TABLE ComputedColumnTestPersisted (        ComputedColumnTestID int NOT NULL,        QuantityOnHand int NULL,        Cost money NULL,        InventoryCost AS (QuantityOnHand * Cost) PERSISTED, CONSTRAINT PK_ComputedColumnTest_ComputedColumnTestID PRIMARY KEY CLUSTERED (ComputedColumnTestID) );

Guidelines with Computed and Persisted Columns

Some general guidelines to consider when using both computed and persisted columns relate to their cost in terms of resource usage. The whole purpose of using these features is to improve the performance of the database, but occasionally they can have a detrimental effect.

Use a computed column when data is derived from other columns using simple mathematical calculations. This is especially true for tables that hold relatively static data.

• Avoid the use of complex calculations or functions in computed columns. The overhead cost of complex calculations in any computed columns could result in poorer performance of the table.

• Avoid the use of persisted columns on dynamic data.

  A table with frequent inserts, updates, and deletes within a table will cause significant overhead on a persisted computed column as the data is constantly recalculated and stored. Generally, frequently queried data perform better as a persisted computed column, while frequently updated data perform better as a nonpersisted computed column.

• Use error-checking to protect against mathematic errors.

  As an example, a check for zero or null data before performing a divide will avoid a divide by zero error.