The following examples illustrate both the usefulness and the dangers of using nondeterministic computed columns and index keys. They are intended only for illustrative purposes.
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.
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, this statement always returns the same result, if the data in the table does not change:
select Cust_ID, Property_ID from Renting where Formatted_Name ='RICHARD HUANG'
Adaptive Server does not guarantee repeatable reads on nondeterministic virtual computed columns. For example, in this query, the column Rent_Due returns different results on different days; the column has a serial time property, for which the value is a function of the amount of time that passes between rent payments:
select Cust_Name, Rent_Due from renting where Cust_Name= 'RICHARD HUANG'
The nondeterministic property is useful here, but use it with caution. For instance, if you inadvertently 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 in this query, 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, no matter how 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 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.
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 Adaptive Server 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 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.
