Computed column Usage

標籤：

顧名思義，計算資料行是通過計算產生值得列，Computed_Column_Name as Computed_Expression，計算資料行的資料類型是由計算運算式的結果確定的。

計算資料行有兩個特性：Persisted，Deterministic 和自動確定Data Type

1, 如果沒有為計算資料行指定Persisted 關鍵字，那麼計算資料行是不會實體儲存體的，是個Virtual Column，只儲存計算的邏輯，而不佔用實體儲存體空間；如果為計算資料行指定Persisted 關鍵字，那麼計算資料行將佔用實體儲存體空間，將計算值儲存在物理裝置中。

2, 根據計算資料行運算式的不同，可以將計算資料行區分為可確定性和不可確定性。Deterministic 是指Computed Column 的value 是能確定下來的，如果該row中的相關聯column沒有變化，那麼Computed Column 的value是不變的；

3, 在建立計算資料行時，不能指定Data type， 計算資料行的資料類型是由計算運算式的結果確定的。

nullability 需要顯示指定，否則，SQL Server 預設是 nullable。

 

Example：使用binary_checksum計算多個字串 columns 的CheckSum，只需要進行int類型的比較，速度會快很多。

if object_id(N‘dbo.City_Staging‘,N‘U‘) is not nulldrop table dbo.City_Stagingcreate table dbo.City_Staging(    [Country] [nvarchar](512) NOT NULL,    [State] [nvarchar](512) NOT NULL,    [City] [nvarchar](512) NOT NULL,    [Value_binary_checksum] as binary_checksum(                    lower(ltrim(rtrim([Country]))),                    lower(ltrim(rtrim([State]))),                    lower(ltrim(rtrim([City])))                    ) persisted not null)with(data_compression=page);

Appendix：MSDN Syntax

<computed_column_definition> ::= column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ][     [ CONSTRAINT constraint_name ]    { PRIMARY KEY | UNIQUE }        [ CLUSTERED | NONCLUSTERED ]        [             WITH FILLFACTOR = fillfactor           | WITH ( <index_option> [ , ...n ] )        ]        [ ON { partition_scheme_name ( partition_column_name )         | filegroup | "default" } ]    | [ FOREIGN KEY ]         REFERENCES referenced_table_name [ ( ref_column ) ]         [ ON DELETE { NO ACTION | CASCADE } ]         [ ON UPDATE { NO ACTION } ]         [ NOT FOR REPLICATION ]     | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) ] 

computed_column_expression                                

Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. 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 or contain alias data types.

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 must be marked PERSISTED to participate in a FOREIGN KEY or CHECK constraint.

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

Based on the expressions that are used, the nullability of computed columns is determined automatically by the Database Engine. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows also produce NULL results. 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 with the check_expression constant, where the constant is a nonnull value substituted for any NULL result. REFERENCES permission on the type is required for computed columns based on common language runtime (CLR) user-defined type expressions.

PERSISTED               

Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED lets you create an index on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns that are used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

 

Computed column Usage