9

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?

madprog
  • 275
  • 1
  • 4
  • 13
  • Don't know. I would have expected `is_computed` in `sys.columns` to show this but doesn't seem to for views and I don't see anything in [`COLUMNPROPERTY`](http://msdn.microsoft.com/en-us/library/ms174968.aspx) either. – Martin Smith Mar 06 '13 at 14:55
  • `is_computed` was my first guess too, but [Microsoft's definition](http://msdn.microsoft.com/en-us/library/ms191250(v=SQL.105).aspx) is: `A computed column is computed from an expression that can use other columns in the same table.` Here it is not the case, as the column of the view uses a column from the underlying table, not from the view itself. – madprog Mar 06 '13 at 15:08

2 Answers2

2

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...

ljh
  • 2,546
  • 1
  • 14
  • 20
  • 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 `select`s will flag all the columns derived, but will be found at the middle of the query. – madprog Mar 07 '13 at 09:35
1
  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.

ljh
  • 2,546
  • 1
  • 14
  • 20