computed columns have some benefits, along with some restrictions (like being deterministic). what's interesting is that SQL doesn't necessarily store the computed column data...
If you use the same computation in many queries against the same table, storing the computation in the base table as a computed column shortens your queries and results in less required code maintenance. For example, suppose you need to calculate the gross and net values of each order row in the Northwind sample database's Order Details table. You could perform the computations as part of your query as follows:
SELECT
OrderID,
ProductID,
Quantity,
Quantity * UnitPrice AS GrossValue,
Quantity * UnitPrice * (1 - Discount) AS NetValue
FROM [Order Details]
Or, if you have many different queries that retrieve the gross and net values, you might prefer to add GrossValue and NetValue computed columns to the Order Details table:
ALTER TABLE [Order Details]
ADD GrossValue AS Quantity * UnitPrice,
NetValue AS Quantity * UnitPrice * (1 - Discount)
Now you can refer to the GrossValue and NetValue columns in the same way that you'd refer to any other columns in your table:
SELECT OrderID, ProductID, Quantity, GrossValue, NetValue
FROM [Order Details]
Adding a computed column to a table doesn't necessarily mean that SQL Server stores the computation result on disk. By default, SQL Server evaluates the computation at runtime, when the query that refers to the computed column is invoked. SQL Server 7.0 introduced computed columns; SQL Server 2000 added the ability to create indexes on computed columns, so the results can be stored on disk. An index on a computed column saves the CPU time needed to perform the calculations and allows efficient filtering and sorting where appropriate.
some restrictions on computed columns :
Creating Indexes on Computed Columns
Indexes can be defined on computed columns, provided these requirements are met:
The computed_column_expression must be deterministic. Expressions are deterministic if they always return the same result for a given set of inputs. computed_column_expression is deterministic if:
All functions referenced by the expression are deterministic and precise. This includes both user-defined and built-in functions. For more information, see Deterministic and Nondeterministic Functions.
All columns referenced in the expression come from the table containing the computed column.
No column reference pulls data from multiple rows. For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.
The IsDeterministic property of the COLUMNPROPERTY function reports whether a computed_column_expression is deterministic.
A computed column expression is precise if:
It is not an expression of the float data type
It does not use in its definition a float data type. For example, in the following statement, column y is int and deterministic, but not precise:
CREATE TABLE t2 (a int, b int, c int, x float,
y AS CASE x
WHEN 0 THEN a
WHEN 1 THEN b
ELSE c
END)
The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise.
Note Any float expression is considered nonprecise and cannot be a key of an index; a float expression can be used in an indexed view but not as a key. This is true also for computed columns. Any function, expression, user-defined function, or view definition is considered non-deterministic if it contains any float expressions, including logical ones (comparisons).
The ANSI_NULL connection-level option must be set to ON when the CREATE TABLE statement is executed. The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.
The computed_column_expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
The connection on which the index is created, and all connections attempting INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.
These options must be set to ON:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER
In addition to these ON settings, the NUMERIC_ROUNDABORT option must be set to OFF. For more information, see SET Options That Affect Results.
Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server 2000?
A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns, you can create UNIQUE and PRIMARY KEY constraints on computed columns.
Defining a UNIQUE constraint on a computed column is a straightforward process, as the following example shows:
CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)
However, if you define a PRIMARY KEY on a computed column, such as:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)
you receive the following error:
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'T2'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Because of the primary key constraint, SQL Server requires you to guarantee that your computation's result will not be NULL. The computation in the computed column can overflow (for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from the smallest integer), and other computations can result in a divide-by-zero error. However, if the ARITHABORT (which determines whether a query has ended when an overflow or a divide-by-zero error occurs) and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error conditions) session settings are off, instead of ending the query, the computation can have a NULL result.
In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot create an index on a computed column or insert values into a table that has an index on a computed column because SQL Server detects such an attempt and returns an error. But SQL Server still requires you to guarantee that the computation will not result in NULL values. The trick is to wrap the computed column's computation with the ISNULL() function and supply an alternative value if the computation results in NULL:
CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)