If you are migrating to Adaptive Server 15.0 and considering computed columns, you must understand when computed columns are evaluated, particularly if you are trying to predetermine the likely output of non-deterministic columns. These are the evaluation rules:
The expressions for nonmaterialized (virtual) computed columns are evaluated during query processing, so it reflects the state of the current user’s session.
The expressions for materialized (physical) computed columns are evaluated only when a referenced column is modified.
For example, this table has three pairs of computed columns, and each are evaluated differently:
create table test_table ( rownum int not null, status char(1) not null, -- virtual columns sel_user as suser_name(), sel_date as getdate(), -- materialized columns cr_user as suser_name() materialized, cr_date as getdate() materialized, upd_user as (case when status is not null then suser_name() else 'dbo' end) materialized, upd_date as (case when status is not null then getdate() else 'jan 1 1970' end) materialized )
sel_user and sel_date – are virtual columns evaluated when a user queries the table.
cr_user and cr_date – physical and materialized columns that do not reference any other column. Their expression is evaluated only when rows are inserted. They are not affected by updates.
upd_user and upd_date – these columns reference the status column, although the status column does not determine the value. These columns are changed only if the status column is modified by inserts and updates that set the status column to any value.
As a result of the evaluation, the last two computed column pairs, cr_user and cr_date, and upd_user and upd_date are unaffected by queries. Although they are based on nondeterministic functions, the values are consistent for all queries.
