January 19, 2009 at 11:14 am
I wrote this view a while back whose only purpose is to add a single field from anther table that we often queried on to the end of a whole table. Very simple, probably shouldn't have ever been done, but that's another issue. Here's what the view definition looked like.
CREATE VIEW Table1wCorp_Cust
AS
SELECT T1.*, Corp_Cust -- This field comes from the second table. Note, no alias, but the field only exists in T2.
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Something = T2.Something
Now it's 6 months down the road, and suddenly, everything referencing this view blows up. I have just added 3 new fields to Table1. None of these fields is named Corp_Cust, but the first of 3 fields I added was an INT, where as corp_cust was a varchar. The SQL of the view looks exactly the same, but if you look at design mode of the view, you have this:
SELECT (... whole bunch of fields...),
T1.NewlyAddedINTField1 AS Corp_Cust,
T1.NewlyAddedVarcharField2,
T1.NewlyAddedVarcharField3,
T2.Corp_Cust
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Something = T2.Something
Changing the base view definition to include an alias on the Corp_Cust field fixes the problem. My question is... how does this view get corrupted in the first place? While it's good practice to add table aliases, I didn't think something like this would blow up without them.
Can anyone explain this?
Notes:
You could not even select out of the view with a criteria of WHERE corp_cust = 'SomeVarchar' after it became corrputed. I'm assuming it's because the *actual* corp_cust field was the second with that name in the query, and thus had no column name.
The fields WERE added to the table through a client side application that made a database modification, not sure if that's a factor here or not.
January 19, 2009 at 1:11 pm
Hey Seth:
I was able to duplicate your problem in my sandbox db and found that simply doing an ALTER VIEW with the original specs from your CREATE VIEW solved the problem without changing anything else.
If you look at syscomments before doing a "design" on the view, you will find that the syscomments still has the original specifications from the Create View. Apparently the aliasing is done by the "design" interface trying to reconcile the new columns.
When you first created your view it displayed a fixed number of columns. When you added columns to Table1, the view did not dynamically increase the number of output columns. What it did was read in the new Table1 columns before it read the column from Table2, but kept a static number of output columns and output column headings. I tested including two columns from Table2 and only adding one column to table 1. It is just moving values to the right as new columns get added. Again, the column headings are fixed and do not change. The "excess" columns are not displayed.
If you were to REMOVE columns from Table 1, you would get the following message:
Msg 4502, Level 16, State 1, Line 2
View or function 'scoreAppr' has more column names specified than columns defined.
You would think that it would give a similar error if there were more column names defined than specified, but I suspect that the select * prevents testing for this. Someone who knows the internals much better than me might be able to help us out on this one. Until then, it should be law that views always be recompiled whenever the underlying table schema are changed.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 19, 2009 at 1:35 pm
If that's the case, then any time the definition for Table1 changes, that view will have to be altered, unless there is another option that I can set to stop this from happening.
January 19, 2009 at 1:44 pm
You'll want to peruse this BOL entry. It has to do with flagging your views as schemabinding to prevent such issues...
http://msdn.microsoft.com/en-us/library/ms187821.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 1:50 pm
... and here it is in online help. It's a pain to have to drop all views before you can alter a table, but it prevents situations like the one above. (search page for 'SCHEMABINDING ')
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/aecc2f73-2ab5-4db9-b1e6-2f9e3c601fb9.htm
Long story short is that, either way, you have to recompile views after altering tables.
I know I'm preaching to the choir, but select * is just bad practice.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 19, 2009 at 1:55 pm
Thanks Matt. I've always known you could Schema Bind a view, but I've never needed to, so I didn't consider the reasons why you might do so. In this particular case, I think schema binding to be too strong an option as that would effectively lock down two tables for the sake of a view that could easily be done without, but I'll make note of that sp to refresh the view metadata.
If we were actually using 2005 (Which hopefully we will be in a couple months) I'm assuming I could use a DDL trigger to auto execute this sp on a change to the underlying table?
January 19, 2009 at 2:01 pm
Bob Hovious (1/19/2009)
I know I'm preaching to the choir, but select * is just bad practice.
I agree. I was just considering re-writing this to manually list all of the field names. It would still cause the same issues if a column was removed, but that's a lot less likely than more columns being added.
My original thought when i did it was that using SELECT * would make this self-maintaining in that any added or removed columns would be automatically reflected in the view(and I did want all columns, because I wanted the view to be a full copy of Table1 with the added field), but since I now realize that is not the case (at least until we can use the DDL triggers, if those would work for this), I no longer see any reason not to explicitly list the field names.
January 19, 2009 at 2:09 pm
Garadin (1/19/2009)
Thanks Matt. I've always known you could Schema Bind a view, but I've never needed to, so I didn't consider the reasons why you might do so. In this particular case, I think schema binding to be too strong an option as that would effectively lock down two tables for the sake of a view that could easily be done without, but I'll make note of that sp to refresh the view metadata.If we were actually using 2005 (Which hopefully we will be in a couple months) I'm assuming I could use a DDL trigger to auto execute this sp on a change to the underlying table?
I'm assuming you could, yes. Can't say I have actually tried though.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 19, 2009 at 2:30 pm
I believe that our DBA group prefers to simply have the views listed/flagged for review before changes are pushed to production. Apparently they prefer to control the sequence to make all table changes, before making changes to views. This makes sense if you consider that more than one table schema might change which would affect the same view.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 19, 2009 at 7:05 pm
Not sure it's appropriate for all of this, but if you mearly add columns to a table and the view doesn't need to be changed because it doesn't use the added columns, you can just do an [font="Arial Black"]sp_RefreshView 'ViewName'[/font].
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2009 at 7:08 pm
Or, if you insist on using "*" in views, always make sure that there are no fields after it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 20, 2009 at 6:40 am
Doh :w00t: put the T2 column(s) at the front.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply