Examples

The following examples illustrate both the usefulness and the dangers of using nondeterministic computed columns and index keys. All examples in this document are intended only for illustrative purposes.

CREATE TABLE Renting
     (Cust_ID INT, Cust_Name VARCHAR(30),
     Formatted_Name COMPUTE format_name(Cust_Name),      Property_ID INT,Property_Name COMPUTE 
     get_pname(Property_ID), start_date COMPUTE
     today_date()MATERIALIZED, Rent_due COMPUTE
     rent_calculator(Property_ID, Cust_ID,      Start_Date))

The table Renting in this example stores rental information on various properties. It contains these fields:

Formatted_Name, Property_Name, Start_Date, and Rent_Due are defined as computed columns.

• Formatted_Name – virtual computed column that transforms the customer name to a standard format. Since its output depends solely on the input Cust_Name, Formatted_Name is deterministic.

• Property_Name – virtual computed column that retrieves the name of the property from another table, Property. Property is defined as:

  CREATE TABLE Property
       (Property_ID INT, Property_Name VARCHAR(30),       Address VARCHAR(50), Rate INT)
  

  To get the property name based on the input ID, the function get_pname invokes a JDBC query:

  SELECT Property_Name from Property where Property_ID=input_ID
  

  The computed column Property_Name looks deterministic, but it is actually nondeterministic, because its return value depends on the data stored in the table Property as well as on the input value Property_ID.

• Start_Date – a nondeterministic user-defined function that returns the current date as a varchar(15). It is defined as materialized. Therefore, it is reevaluated each time a new record is inserted, and that value is stored in the Renting table.

• Rent_Due – a virtual nondeterministic computed column, which calculates the current rent due, based on the rent rate of the property, the discount status of the customer, and the number of rental days.

• Adaptive Server guarantees repeatable reads on deterministic virtual computed columns, even though, by definition, a virtual computed column is evaluated each time it is referenced. For example:

  SELECT Cust_ID, Property_ID from Renting
       WHERE Formatted_Name ='RICHARD HUANG'
  

  This statement always returns the same result, if the data in the table does not change.

• Adaptive Server does not guarantee repeatable reads on nondeterministic virtual computed columns. For example:

  SELECT Cust_Name, Rent_Due from renting 
      where Cust_Name= 'RICHARD HUANG'
  

  In this query, the column Rent_Due returns different results on different days. This column has a serial time property, whose value is a function of the amount of time that passes between rent payments.

  The nondeterministic property is useful here, but you must use it with caution. For instance, if you accidentally defined Start_Date as a virtual computed column and entered the same query, you would rent all your properties for nothing: Start_Date is always evaluated to the current date, so the number of Rental_Days is always 0.

  Likewise, if you mistakenly define the nondeterministic computed column Rent_Due as a preevaluated column, either by declaring it materialized or by using it as an index key, you would rent your properties for nothing. It is evaluated only once, when the record is inserted, and the number of rental days is 0. This value is returned each time the column is referenced.

Adaptive Server guarantees repeatable reads on materialized computed columns, regardless of their deterministic property, because they are not reevaluated when you reference them in a query. Instead, Adaptive Server uses the preevaluated values.

Deterministic materialized computed columns always have the same values, however often they are reevaluated.

Nondeterministic materialized computed columns must adhere to these rules:

• Each evaluation of the same computed column may return a different result, even using the same set of inputs.

• References to nondeterministic preevaluated computed columns use the preevaluated results, which may differ from current evaluation results. In other words, historical rather than current data is used in nondeterministic preevaluated computed columns.

In Example 1, Start_Date is a nondeterministic materialized computed column. Its results differ, depending on what day you insert the row. For instance, if the rental period begins on “02/05/04,” “02/05/04” is inserted into the column, and later references to Start_Date use this value. If you decide to reference this value later, on 06/05/04, the query continues to return “02/05/04,” not “06/05/04,” as you would expect if the expression was evaluated every time you query the column.

Suppose you use the table created in Example 1, Renting, and create an index on the virtual computed column Property_Name, transforming Property_Name into a materialized computed column. Then, you insert a new record:

Property_ID=10

Inserting this new record calls getpname(10) from the table Property, executing this JDBC query:

SELECT Property_Name from Property where Property_ID=10

The query returns “Rose Palace,” which is stored in the data row. This all works, unless someone changes the name of the property by issuing this query:

UPDATE Property SET Property_Name ='Red Rose Palace'
     where Property_ID = 10

The query returns “Rose Palace,” so Adaptive Server stores “Rose Palace.” This update command on the table Property invalidates the stored value of Property_Name in the table Renting, which must also be updated to “Red Rose Palace.” Because Property_Name is defined on the column Property_ID in the table Renting, not on the column Property_Name in the table Property, it is not automatically updated. Future reference to Property_Name may produce incorrect results.

To avoid this situation, create a trigger on the table Property:

CREATE TRIGGER my_t ON Property FOR UPDATE AS
     IF UPDATE(Property_Name)
     BEGIN
     UPDATE Renting SET Renting.Property_ID=Property.Property_ID
          FROM Renting, Property
          WHERE Renting.Property_ID=Property.Property_ID
     END

When this trigger updates the column Property_Name in the table Property, it also updates the column Renting.Property_ID, the base column of Property_Name. This automatic update triggers Adaptive Server to reevaluate Property_Name and update the value stored in the data row. Each time Adaptive Server updates the column Property_Name in the table Property, the materialized computed column Property_Name in the table Renting is refreshed, and shows the correct value.