Calculated Field or Computed field can be created for multiple purposes. It can be created to sum up columns from a table or it receives a function as a data type that can use for any computation or restriction of data. You can use this column to avoid complex calculations at query time.
There are two ways to create calculated fields.
CREATING CALCULATED FIELD THROUGH SSMS.
In this example TotalAmont Column returns data by sum of Price and Other Charges values of each column.
CREATING CALCULATED FIELD THROUGH FUNCATION.
In This screen Part column returns data on the basis of CalcPart function.
USES OF CALCULATED FIELD
1- 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:
1.1- 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.
1.2- 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.
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.
RESTRICTIONS OF CALCULATED FIELD
1- A computed column cannot be the target of an INSERT or UPDATE statement.
There are two ways to create calculated fields.
CREATING CALCULATED FIELD THROUGH SSMS.
In this example TotalAmont Column returns data by sum of Price and Other Charges values of each column.
CREATING CALCULATED FIELD THROUGH FUNCATION.
In This screen Part column returns data on the basis of CalcPart function.
USES OF CALCULATED FIELD
1- 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:
1.1- 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.
1.2- 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.
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.
RESTRICTIONS OF CALCULATED FIELD
1- A computed column cannot be the target of an INSERT or UPDATE statement.
1 comment:
Thanks it helps
Post a Comment