Views that include IDENTITY columns

You can define a view that includes an IDENTITY column by listing the column name, or the syb_identity keyword, in the view’s select statement. For example:

create view sales_view
as select syb_identity, stor_id
from sales_daily

However, you cannot add a new IDENTITY column to a view by using the identity_column_name = identity(precision) syntax.

You can use the syb_identity keyword to select the IDENTITY column from the view, unless the view:

If any of these conditions is true, Adaptive Server does not recognize the column as an IDENTITY column with respect to the view. When you execute sp_help on the view, the column displays an “Identity” value of 0.

In the following example, the row_id column is not recognized as an IDENTITY column with respect to the store_discounts view because store_discounts joins columns from two tables:

create view store_discounts
as
select stor_name, discount
from stores, new_discounts
where stores.stor_id = new_discounts.stor_id

When you define the view, the underlying column retains the IDENTITY property. When you update a row through the view, you cannot specify a new value for the IDENTITY column. When you insert a row through the view, Adaptive Server generates a new, sequential value for the IDENTITY column. Only the table owner, database owner, or system administrator can explicitly insert a value into the IDENTITY column after setting identity_insert on for the column’s base table.