Select * in a view

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'

Check again:

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