Formulas for Computed Column Specification RRS feed

  • Question

  • Is there a place that list possible formulas to insert in the "(FORMULA)" under "Computed Column Specification" when in design mode for a qiven column in SQL 2005 express? (ie.  Email, Phone, Zip, State, etc.)





    Saturday, August 23, 2008 12:08 PM

All replies

  • From http://msdn.microsoft.com/en-us/library/ms174979.aspx:


    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:

    • A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, 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.
    Each row in a table can have different values for columns that are involved in a computed column; therefore, the computed column may not have the same value for each row.

    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.

    The (FORMULA) or computed_column_expression can be a T-SQL expression containing constants, scalar functions, operators and other columns from that table. e.g. to construct mailing address, you can use something like

    "address + ', ' + city + ', ' + state + '   ' + convert(nvarchar(5), zip)", where address, city, state and zip are other columns in the same table.


    Wednesday, August 27, 2008 7:33 PM
  • Will the the computation work if one of the dependencies is an identity column?
    I am having trouble getting the right calculation.... seems to be always 1??


    Giovanni Coppola.

    Friday, September 25, 2009 7:45 AM
  • Yes it can
    Saturday, March 20, 2010 11:28 AM