Examples of Nondeterministic Computed Columns

Examples are provide to illustrate both the usefulness and the dangers of using nondeterministic computed columns and index keys.

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

Cust_ID – ID of the customer
Cust_Name – name of the customer
Formatted_Name – customer’s name
Property_ID – ID of the property rented
Property_Name – name of the property in a standard format
Start_Date – starting day of the rent
Rent_Due – amount of rent due today

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))

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, which 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.

Using the table created in Example 1, Renting: If you create an index on the virtual computed column Property_Name, it becomes a materialized computed column.

If you then inserted a new record:

Property_ID=10

This new record calls get_pname(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:

update Property set Property_Name ='Red Rose Palace'
     where Property_ID = 10

The query returns “Red Rose Palace,” so SAP ASE stores “Red 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 SAP ASE to reevaluate Property_Name and update the value stored in the data row. Each time SAP ASE 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.