A computed column is a column whose value is an expression that can refer to the values of other columns, called dependent columns, in the same row. Computed columns are especially useful in situations where you want to index a complex expression that can include the values of one or more dependent columns. The database server will use the computed column wherever it see an expression that matches the computed column's COMPUTE expression; this includes the SELECT list as well as predicates. However, if the query expression contains a special value, such as CURRENT TIMESTAMP, this matching does not occur. For a list of special values that prevent matching, see Special values.

During query optimization, the SQL Anywhere optimizer automatically attempts to transform a predicate involving a complex expression into one that simply refers to the computed column's definition. For example, suppose that you want to query a table containing summary information about product shipments:

CREATE TABLE Shipments(
   ShipmentID INTEGER NOT NULL PRIMARY KEY,
   ShipmentDate TIMESTAMP,
   ProductCode CHAR(20) NOT NULL,
   Quantity INTEGER NOT NULL,
   TotalPrice DECIMAL(10,2) NOT NULL
);

In particular, the query is to return those shipments whose average cost is between two and four dollars. The query could be written as follows:

SELECT *
   FROM Shipments
   WHERE ( TotalPrice / Quantity ) BETWEEN 2.00 AND 4.00;

However, in the query above, the predicate in the WHERE clause is not sargable since it does not refer to a single base column. See Predicate analysis. If the size of the Shipments table is relatively large, an indexed retrieval might be appropriate rather than a sequential scan. To benefit from an indexed retrieval, create a computed column named AverageCost for the Shipments table, and then create an index on the column, as follows:

ALTER TABLE Shipments
   ADD AverageCost DECIMAL(30,22)
   COMPUTE( TotalPrice / Quantity );
 CREATE INDEX IDX_average_cost
   ON Shipments( AverageCost ASC );

Choosing the type of the computed column is important; the SQL Anywhere optimizer replaces only complex expressions by a computed column if the data type of the expression in the query precisely matches the data type of the computed column. To determine what the type of any expression is, you can use the EXPRTYPE built-in function that returns the expression's type in ready-to-use SQL terms:

SELECT EXPRTYPE(
 'SELECT ( TotalPrice/Quantity ) AS X FROM Shipments', 1 )
   FROM DUMMY;

For the Shipments table, the above query returns NUMERIC(30,22). During optimization, the SQL Anywhere optimizer rewrites the query above as follows:

SELECT *
   FROM Shipments
   WHERE AverageCost
   BETWEEN 2.00 AND 4.00;

In this case, the predicate in the WHERE clause is now a sargable one, making it possible for the optimizer to choose an indexed scan, using the new IDX_average_cost index, for the query's access plan.

Altering computed column expressions

You can change the expression used in a computed column with the ALTER TABLE statement. The following statement changes the expression that a computed column is based on.

ALTER TABLE table-name
ALTER column-name
SET COMPUTE ( new-expression );

The column is recalculated when this statement is executed. If the new expression is invalid, the ALTER TABLE statement fails.

The following statement stops a column from being a computed column.

ALTER TABLE
table-name
ALTER column-name
DROP COMPUTE;

Existing values in the column are not changed when this statement is executed, but they are no longer updated automatically.