Views gone wild.

Oh no, my data is gone, there is serious corruption, run for the hills, abandon ship! Where is my passport! WHY DID we do this upgrade???? Why did we release this patch? Why is the Database guy so relaxed?

Probably because he/ she haven’t refreshed the views yet. What are these views you speak about?

A view is a dynamic “view” of data in tables. The issue with them are that for practical reasons and sometimes just because of CRAZY AND TOTALLY UNREALISTIC DEADLINES, we use this little thing: * an asterisk, and just like his Gaulish friend Asterix, is a small thing that packs a big punch, but like magic potion it should be used wisely, not wildly.

So, what happens, due to a structural change in the database, new fields get added to tables being referenced by the views. The views have its own list definition of the fields it should contain, and nobody told it to update. As an example a table had 10 columns and now it has 12.

Someone wrote a view that looked something like this :

Create view v_someview as select * from TenColumnTable t inner join SecondTable s on t.foreignkey = s.primarykey

Column 11 and 12 before the upgrade referenced the 1st 2 columns of “basetable”. After the upgrade when you go select * from v_ someview you see the name of column 1 of Secondtable in column 11 of the view, but the data of column 11 of TenColumnTable. How is this possible? Well when a view gets created it saves the underlying structure in the system tables just as a column, and a table function, and that needs to be refreshed for the data to display correctly.

exec sp_refreshview ‘v_ someview’ will do just that and all the madness will end.

So remember, if you ever make changes to a database’s structure, and all hell breaks loose, before you skip the country, refresh the views.

Leave a Reply

Your email address will not be published. Required fields are marked *