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:
Selects the IDENTITY column more than once
Computes a new column from the IDENTITY column
Includes an aggregate function
Joins columns from multiple tables
Includes the IDENTITY column as part of an expression
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.
