CREATETABLECREATETABLE [database_name. [owner]. | owner.] table_name ({column_definition | column_nameAScomputed_column_expression | table_constraint }[,... n]) computing columns are virtual columns that are physically not stored in the table. The calculation column is composed
Create table [database_name. [owner]. | owner.] table_name ({column_definition | column_name AS computed_column_expression | table_constraint }[,... n]) computing columns are virtual columns that are physically not stored in the table. The calculation column is composed
CREATE TABLE
Create table [Database_name.[Owner].|Owner.]Table_name
({<Column_definition>
|Column_nameASComputed_column_expression
| <Table_constraint> }[,...N]
)
ComputingA column is a virtual column physically not stored in a table.ComputingA column is expressed by other columns in the same table.Computing. For example,ComputingColumns can be defined as follows:CostASPrice * qty. The expression can be a non-ComputingThe column name, constant, function, and variable can also be any combination of the above elements connected by one or more operators. The expression cannot be a subquery.
ComputingColumns can be used to select a list, WHERE clause, order by clause, or any other location WHERE regular expressions can be used, except in the following cases:
- ComputingThe column cannot be used as the DEFAULT or foreign key constraint definition, or together with the not null constraint definition. However, ifComputingA column is defined by a deterministic expression and is allowed in the index column.ComputingThe data type of the result. You can use this column as the KEY column in the index or as part of the primary key or UNIQUE constraint.
For example, if the table contains an integer ColumnAAndB, You canComputingColumnA + BCreate an index. But cannotComputingColumnA + DATEPART (dd, GETDATE ())Create an index on, because its value may change in future calls.
- ComputingThe column cannot be the target of an INSERT or UPDATE statement.
DescriptionTableComputingThe column values vary by row, so the values of each rowComputingThe column values may be different.
ComputingColumn null is automatically determined by SQL Server Based on the expression used. Even if there are only columns that cannot be empty, the results of most expressions are considered to be empty, because possible overflow or overflow will also generate NULL results. Use the COLUMNPROPERTY function (AllowsNull attribute) to view anyComputingColumn is null. By specifying ISNULL (Check_expression, Constant), where the constant is a non-NULL value that replaces any NULL results, can be a NULL expressionExprCan be converted to a non-empty expression.
In
ComputingCreate an index on a column
If the following requirements are metComputingColumn-defined index:
- Computed_column_expressionIt must be deterministic. If the expression always returns the same result for a group of given input, the expression is deterministic. In the following cases,Computed_column_expressionIs deterministic:
- All functions referenced by expressions are deterministic and accurate. This includes user-defined functions and built-in functions. For more information, see deterministic and non-deterministic functions.
- All columns referenced by the expression come fromComputingColumn table.
- No column reference pulls data from multiple rows. For example, Aggregate functions (such as SUM or AVG) rely on data from multiple rows, which makesComputed_column_expressionNot sure.
COLUMNPROPERTY FunctionIsDeterministicProperty ReportComputed_column_expressionAre you sure you want.
ComputingColumn expressions are accurate in the following cases:
- NonFloatData Type expression.
- The float data type is not used in its definition. For example, in the following statementYIsIntBut not accurate:
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)
COLUMNPROPERTY FunctionIsPreciseProperty ReportComputed_column_expressionAccurate or not.
DescriptionAnyFloatExpressions are considered inaccurate and cannot be used as index keys;FloatExpressions can be used in the index view, but cannot be used as keys. ForComputingThe same applies to columns. If a function, expression, or user-defined function or view definition contains anyFloatExpressions (including logical expressions (comparison) are considered uncertain.
- When executing the create table statement, you must set the ANSI_NULL connection level option to ON. OBJECTPROPERTY FunctionIsAnsiNullsOnProperty reports whether this option is enabled.
- IsComputingColumn-definedComputed_column_expressionCannot be setText,NtextOrImageData type.
- The six SET options must be SET to ON and one option must be SET to OFF for the connection between creating an index and all the connections that attempt to use INSERT, UPDATE, or DELETE statements to change the index value. If any SELECT statement is executed for connections that do not have the preceding options, the optimizer ignoresComputingThe index of the column.
The following options must be set to ON:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
In addition to the preceding ON settings, the NUMERIC_ROUNDABORT option must be set to OFF. For more information, see SET options that affect the results.
©1988-2004 Microsoft Corporation. All rights reserved.