How to determine if a column of a view is derived or constant?

Question

Let's say I have the following table:

create table t_Item (
    ItemID int not null identity(1,1) constraint PK_Item primary key,
    Description varchar(256) not null,
    Price decimal(10,2) not null
)

and the following view:

create view Item as
  select ItemID
        ,Description
        ,Price
        ,1.09 Tax
        ,Price * 1.09 TaxedPrice
    from t_Item

TaxedPrice is a derived column, and Tax is a constant column.

Therefore, I can't insert or update any of them. The first following query would pass, whereas the other ones would fail with an error.

insert into Item (Description, Price) values ('Test item', 14.00)

insert into Item (Description, Price, TaxedPrice) values ('Test item', 14.00, 15.26)

insert into Item (Description, Price, Tax) values ('Test item', 14.00, 1.09)

And here is the returned error message:

Update or insert of view or function 'Item' failed because it contains a derived or constant field.

Is there a way, maybe with the system views, to list the view columns which must not be updated?

Solution 1

Looks like there is no system view saves information you are looking for. You can find out derived column or constant column by parsing the view definition or by exception handling...not good, but didn't find other ways...

Solution 2

1. Any columns come from the following aggregation functions, considered as derived column. AVG, COUNT,SUM,MIN,MAX,GROUPING,STDEV,STDEVP,VAR,VARP
2. If the view definition contains following syntax, all columns considered as derived column. UNION, UNION ALL, CROSSJOIN, EXCEPT, INTERSECT
3. The columns affected by GROUP BY, HAVING, DISTINCT, are also considered as derived column.

I don't think this cover all scenarios, but a start point to write the parser.

Comments

[madprog]

I was afraid so… Did you find a quick way to extract the list of derived columns from the view definition? This looks like a complex problem: for instance, a union between two selects will flag all the columns derived, but will be found at the middle of the query.