sql - 如何创建从另一列计算的列？

Question

我需要创建一个列 age在 SQL Server 数据库中。

此列的值应根据列 DOB 的值计算。 .

它的值也应该增加为 Age增加。

Answer 1

您应该使用计算列来解决此问题。定义与此类似的东西:

ALTER TABLE Customers ADD Age AS datediff(year, DOB ,getdate())

来自 BlackWasp 的原始声明和更多信息.

编辑:

MSDN 将计算列解释为:

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, and any combination of these connected by one or more operators. The expression cannot be a subquery.

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 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.

Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change > in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.

来源: MSDN - Computed Columns