Computed columns allow you to create a shorthand term for an expression, such as “Pay” for “Salary + Commission,” and to make that column indexable, as long as its datatype is indexable. Nonindexable datatypes include:
text
unitext
image
Java class
bit
Computed columns are intended to improve application development and maintenance efficiency. By centralizing expression logics in the table definition, and giving expressions meaningful aliases, computed columns make greatly simplified and readable queries. You can change expressions by simply modifying the computed column definitions.
Computed columns are particularly useful when you must index a column for which the defining expression is either a nondeterministic expression or function, or which calls a nondeterministic expression or function. For example, getdate always returns the current date, so it is nondeterministic. To index a column using getdate, build a materialized computed column and then index it:
create table rental (cust_id int, start_date as getdate()materialized, prod_id int) create index ind_start_date on rental (start_date)
An important feature of computed columns is that you can use them to compose and decompose complex datatypes (for example, XML, text, unitext, image, and Java classes). 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 decomposing or composing of these complex datatypes when you define the table. For example, suppose you want to store XML “order” documents in a table, along with some relational elements: order_no, part_no, and customer. Using create table with the compute and materialized parameters, you can define an extraction with computed columns:
create table orders(xml_doc image,
order_no compute xml_extract("order_no", xml_doc)materialized,
part_no compute xml_extract ("part_no", xml_doc)materialized,
customer compute xml_extract("customer", xml_doc)materialized)
Each time you insert a new XML document into the table, the document’s relational elements are automatically extracted into the computed columns.
Or, to present the relational data in each row as an XML document, specify mapping the relational data to an XML document using a computed column in the table definition. For example, define a table:
create table orders (order_no int,part_no int, quantity smallint, customer varchar(50))
Later, to return an XML representation of the relational data in each row, add a computed column using alter table:
alter table orders add order_xml compute order_xml(order_no, part_no, quantity, customer)
Then use a select statement to return each row in XML format:
select order_xml from orders
Computed columns support comparison, order by, and group by ordering of complex datatypes, such as XML, text, unitext, image, and Java classes. You can use computed columns to extract relational elements of complex data, which you can use to define ordering.
You can also use computed columns to transform data into different formats, to customize data presentations for data retrieval. This is called user-defined sort order. For example, this query returns results in the order of the server’s default character set and sort order, usually ASCII alphabetical order:
select name, part_no, listPrice from parts_table order by name
Use computed columns to present your query result in a case-insensitive format, such as ordering based on special-case acronyms, as in the ordering of stock market symbols, or using system sort orders other than the default. To transform data into a different format, use either the built-in function sortkey, or a user-defined sort-order function.
For example, to add a computed column called name_in_myorder with the user-defined function Xform_to_myorder():
alter table parts_table add name_in_myorder compute Xform_to_myorder(name)materialized
To return 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 ordering data and create indexes on it.
If you prefer, you can do the same thing using data manipulation language (DML):
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 ordering data and create indexes on it, which improves the performance of the query.
Typical decision-support system applications require intensive data manipulation, correlation, and collation data analysis. Such applications frequently use expressions and functions in queries, and special user-defined ordering is often required. Using computed columns and function-based indexes simplifies the tasks necessary in such applications, and improves performance.
