Last week i read a comment or a blog somewhere (i can’t remember it anymore but please let me know so i can credit!), that a select * in a view gets a full definition under the covers and doesn’t get updated when the underlying table is updated.
So, i decided to take that for a test, see what i can reproduce. I’m using an Azure SQL DB for this test:
-- step 1: create a database create database viewtest; -- step 2: create a table use viewtest; go create table very_important ( id int identity(1,1) primary key, valuable_data varchar(100) null ) -- step 3: add a record to the table insert into very_important (valuable_data) values ('too important to show!'); GO -- step 4: create a view on the table create view vw_very_important as select * from very_important; GO
Nothing special here. Let’s get the data from the view!
-- Step 5: check the view output Select * from vw_very_important
Time for lunch!
Well, just then the CEO walks in and needs an extra column. Now. With NoWait ;).
-- Step 6: add column to table alter table very_important add invaluable_data int default 200;
Even though the cantine is closing in 5 minutes, you decide to check the data. Because, well you know, C-level people. Even though nothing can go wrong.
-- step 7: check the values from view and table select * from very_important select * from vw_very_important
Well… erm… Not good!
Let’s run the query again, with an execution plan. Grant Fritchey will be proud!
Well, this isn’t helping. At first. We need to check out the properties of the clustered index. Hit F4 and select the Clustered index scan operator. Look at the right side of your screen.
Even though my table got updated, the view just didn’t notice. It kept the table definition at the moment of creation. A proud moment but nevertheless, things change. But views… don’t.
For the geeks, if you take a look at the exection plan XML, you’ll see this:
<DefinedValues> <DefinedValue> <ColumnReference Database="[viewtest]" Schema="[dbo]" Table="[very_important]" Column="id" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[viewtest]" Schema="[dbo]" Table="[very_important]" Column="valuable_data" /> </DefinedValue> </DefinedValues>
Defined values. The definition of the view.
Now, how to solve this.
-- Step 9 refresh view definition exec sp_refreshview 'vw_very_important'
-- step 10: please be good select * from very_important select * from vw_very_important
Issue fixed! Now, you’ve deserved your lunch. After lunch, time to rebuild your views where you will remove the select * statements and add schemabinding to prevent mismatches between tables and views. Especially your third-party vendors will love you for schemabinding when they want to update the software.
Thanks for reading! Thoughts? Let me know!