Computed columns

Computed columns were introduced in Sybase ASE 15 to provide easier data manipulation and faster data access.
Computed columns are columns that are defined by an expression. This expression can be built by combining regular columns in the same row and may contain functions, arithmetic operators, case expressions, global variables, Java objects, and path names.

Example:
create table parts_table
(
part_no int,
name char(30),
list_price money,
quantity int,
total_cost compute quantity*list_price
)

In the above example, total_cost is a computed column defined by an arithmetic operation between the columns list_price and quantity. The datatype of total_cost is automatically inferred from the computed column expression. Each system datatype has a datatype hierarchy, which is stored in the systypes system table. The datatype hierarchy determines the results of computations using values of different datatypes. The result value is assigned the datatype that has the lowest hierarchy. In this example, total_cost is a product of the money and int datatypes. It has a hierarchy of 15 and money has a hierarchy of 11. Therefore, the datatype of the result is money.

Syntax: Create table
create table [database.[owner].]  table_name
  (column_name{datatype
  | {compute | as}  computed_column_expression
   [materialized | not materialized] }

Syntax: Alter Table
alter table
   add  column_name  {datatype  | [{ [compute | as}
   computed_column_expression
   }…
   | modify  column_name  {datatype  [null | not null]
   {materialized | not materialized} [null |not null] | {compute | as
   computed_column_expression
   [ materialized | not materialized]}…

Materialization and deterministic characteristics are important concepts to understand behavior of the computed columns.

Materialization:
Materialized columns: are preevaluated and stored in the table when base columns are inserted or updated. The values associated with the computed columns are stored in both the data row and the index row.

Note: A materialized column is reevaluated only when one of its base columns is updated.

Example: Assuming getdate() is 15 Mar 2012, getdate() also would be same.
create table employee_detail
   (emp_id int, join_date as getdate() materialized,   last_update_date datetime)

insert into employee_detail (emp_id, last_update_date)
values (1,             getdate()              )

computed_columns_2.jpg

Its displaying join_date as ‘15/03/2012 9:31:54:036 AM’, that means it is giving same data value for join_date column, as we inserted in this column on 15 mar 2012.

virtual columns(not materialized):  If a column is virtual, or not materialized, its result value must be evaluated each time the column is accessed. This means that if the virtual computed column expression is based on, or calls, a nondeterministic expression, it may return different values each time you access it. You may also encounter run-time exceptions, such as domain errors, when you access virtual computed columns. The concept of nonmaterialized columns is similar to “views,” where the definition of the view is evaluated each time the view is called.

Example: Assuming getdate() is 15 Mar 2012.

create table employee_detail_notmaterialized
   (emp_id int, join_date as getdate()not materialized,   last_update_date datetime)

insert into employee_detail_notmaterialized (emp_id, last_update_date)
                                           values (1,             getdate()              )

Assume we are running below query on 16Mar 2012. (After one day of insert)

select * from employee_detail_notmaterialized
computed_columns_3.jpg

Its displaying join_date as ‘16/03/2012 9:31:54:036 AM’, that means it is giving current value of getdate() for join_date column.

A computed column is defined as materialized or nonmaterialized during the create table or alter table process using the keyword materialized and not materialized after the column name.

Note: A nonmaterialized, or virtual, computed column becomes a materialized computed column once it is used as an index key.
Note: The default is nonmaterialized.

Deterministic Property:

A deterministic algorithm will always produce the same output for a given set of inputs. Expressions and functions using deterministic algorithms exhibit a deterministic property. On the other hand, nondeterministic expressions may return different results each time they are evaluated, even when they are called with the same set of input values.

A good example of a nondeterministic function is the getdate() function. It always returns the current date, which is different each time the function is executed. Any expression built on a nondeterministic function will also be nondeterministic. For example, age (getdate() minus date of birth) will also be nondeterministic. Also, if a function’s return value depends on factors other than input values, the function is probably nondeterministic. A nondeterministic function need not always return a different value for the same set of inputs. It just cannot guarantee the same result each time.

Relationship between Deterministic Property and Materialization:
Deterministic and Materialized Computed Columns: Deterministic materialized computed columns always have the same values; however, often they are reevaluated.

Deterministic and Nonmaterialized Computed Columns: Nonmaterialized columns can be either deterministic or nondeterministic. A deterministic nonmaterialized column always produces repeatable results, even though the column is evaluated each time it is referenced.

Nondeterministic and Materialized Computed Columns: Nondeterministic and materialized computed columns result in repeatable data. They are not reevaluated when referenced in a query. Instead, Adaptive Server uses the preevaluated values.

Nondeterministic and Nonmaterialized Computed Columns: Nonmaterialized columns that are nondeterministic do not guarantee repeatable results.

Benefits of Using Computed Columns:
Provide Shorthand and Indexing for an Expression:
Computed columns allow you to create a shorthand term for an expression. For example, “Age” can be used for “getdate –DateOfBirth.” The computed columns can be indexed as long as the resulting datatype can be in an index. The datatypes that cannot be indexed include text, image, Java class, and bit.

Composing and Decomposing Datatypes:
Computed columns can be used to compose and decompose complex datatypes. You can use computed columns either to make a complex datatype from simpler elements (compose), or to extract one or more elements from a complex datatype (decompose). Complex datatypes are usually composed of individual elements or fragments. You can define automatic decomposition or composition of complex datatypes when you define the table.

User-defined Sort Order:
Computed columns can be used to transform data into different formats — to customize data presentations for data retrieval. This is called user-defined sort order.
You can use computed columns to present your query result in a case-insensitive format, or you can use system sort orders other than the default sort order. To transform data into a different format, use either the built-in function sortkey or a user-defined sort order function.
For example, you can add a computed column called name_in_myorder with a user-defined function Xform_to_myorder():

alter table parts_table add name_in_myorder compute
Xform_to_myorder(name)materialized

The following query then returns the result in the customized format:
select name, part_no, listPrice from parts_table order by
   name_in_myorder

This approach allows you to materialize the transformed and ordered data and create indexes on it. You can do the same thing using data manipulation language (DML), specifying the user-defined function in the select statement:
select name, part_no, listPrice from parts_table
   order by Xform_to_myorder(name)

However, using the computed column approach allows you to materialize the transformed and ordered data. Because materialized columns can be indexed, the query will have improved performance.

Rules and Properties of Computed Columns

  • The datatype of a computed column is automatically inferred from its computed_column_expression.
  • You can define triggers only on materialized computed columns; they are not allowed in virtual computed columns.
  • Computed columns cannot have default constraints.
  • Computed_column_expression can only reference columns in the same table.
  • You cannot use a virtual computed column in any constraints.
  • You can use a materialized computed column as a key column in an index or as part of a unique or primary constraint. However, this can only be done if the computed column value is a deterministic expression and if the resultant datatype is allowed in index columns.
  • You can constrain nullability only for materialized computed columns. If you do not specify nullability, all computed columns are nullable by default; virtual computed columns are always nullable.
  • If a user-defined function in a computed column definition is dropped or becomes invalid, any operations that call that function fail.
  • You cannot change a regular column into a computed column, or a computed column into a regular column.
  • You cannot drop or modify the base column referenced by a computed column.
  • When you add a new computed column without specifying nullability, the default option is nullable.
  • When adding a new materialized computed column, the computed_column_expression is evaluated for each existing row in the table, and the result is stored in the table.
  • You can modify the entire definition of an existing computed column. This is a quick way to drop the computed column and add a new one with the same name. When doing this, keep in mind that a modified column behaves like a new computed column. The defaults are nonmaterialized and nullable if these options are not specified.
  • You can add new computed columns and add or modify their base columns at the same time.
  • When you change a not-null, materialized computed column into a virtual column, you must specify null in the modify clause.
  • You cannot change a materialized computed column into a virtual column if it has been used as an index key; you must first drop the index.
  • When you modify a nonmaterialized computed column to materialized, the computed_column_expression is evaluated for each existing row in the table. The result is stored in the table.
  • If you modify computed columns that are index keys, the index is rebuilt.
  • You cannot drop a computed column if it is used as an index key.
  • You can modify the materialization property of an existing computed column without changing other properties, such as the expression that defines it.

 

 

This entry was posted in RDBMS concepts, SQL, Sybase ASE and tagged , , , . Bookmark the permalink.

2 Responses to Computed columns

Leave a Reply