SQL Server View does not show newly added columns

Few months back, this was experienced by one of my clients and I was asked the reason for this. Although I wanted to make a post on it, I could not and forgot but this popped up again while I was going through notes related to my class.

How can this be happened? Assume that you have created a view that references a table called Customer using SELECT * statement. When you access the view with SELECT statement, it returns all columns in Customer table.

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. CREATE TABLE dbo.Customer  
5. (  
6.  CustomerId int identity(1,1) Primary key  
7.  , Name varchar(200) not null  
8. );  
9. GO  
10.   
11. CREATE OR ALTER VIEW dbo.vwCustomer  
12. AS  
13. SELECT * FROM dbo.Customer;  
14. GO  
15.   
16. SELECT * FROM dbo.vwCustomer;  

USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) Primary key
 , Name varchar(200) not null
);
GO

CREATE OR ALTER VIEW dbo.vwCustomer
AS
SELECT * FROM dbo.Customer;
GO

SELECT * FROM dbo.vwCustomer;

Next, you add a new column to the table and you expect to see all columns including the newly added one when retrieve records using the view.

view plainprint?

1. ALTER TABLE dbo.Customer  
2. ADD CreditLimit decimal(16, 4) not null;  
3. GO  
4.   
5. SELECT * FROM dbo.vwCustomer;  

ALTER TABLE dbo.Customer
ADD CreditLimit decimal(16, 4) not null;
GO

SELECT * FROM dbo.vwCustomer;

As you see, result does not have the newly added column. What is the reason?

This is because of;

1. We have used SELECT * statement inside the view.
2. Metadata of the view has not be updated/refreshed.

If you avoid SELECT * statement when you create views, obviously you do not see this issue because view it is with a defined column set. If you have used SELECT *, then only option is either ALTER the view or refresh the view.

view plainprint?

1. EXEC sp_refreshview 'dbo.vwCustomer';  

EXEC sp_refreshview 'dbo.vwCustomer';

Once the view is refreshed, new column will be appeared in the result.