Help! I added a new column to a table and my view is not showing this new column (with explanation and resolution)

It happens to me once a year at a minimum: I forget that, if I add a new column to a table, I must refresh my views otherwise I will not see that column in my views. Maybe you haven't seen this - if not, you might want to follow along by running the scripts embedded below so that you can reproduce this behavior and learn from it! FYI: this article only applies to non-schema-bound views (which is probably wayyyy in the majority for most sites).

Step 1: Create the base table and a supporting "Get"-type of view:

     USE tempdb

    GO

    CREATE TABLE Employee (

        EmployeeId INT NOT NULL PRIMARY KEY

        , FirstName VARCHAR(128) NOT NULL

        , LastName VARCHAR(128) NOT NULL

        , City VARCHAR(128) NOT NULL

    )

    GO

    INSERT Employee VALUES(1, 'Scott', 'Whigham', 'Dallas')

    INSERT Employee VALUES(2, 'Chad', 'Weaver', 'Dallas')

    INSERT Employee VALUES(3, 'Johnny', 'Evans', 'Manchester')

    INSERT Employee VALUES(4, 'Robson', 'de Souza', 'Manchester')

    GO

    CREATE VIEW GetEmployees AS SELECT * FROM Employee

    GO

    SELECT * FROM GetEmployees

After running the above script, you get a result set showing the four columns: EmployeeId, FirstName, LastName and City. Now let's confuse SQL Server by adding a new column to the table:

    ALTER TABLE Employee ADD Country VARCHAR(128)

    GO

    UPDATE Employee SET Country='USA' WHERE EmployeeId IN (1,2)

    UPDATE Employee SET Country='UK' WHERE EmployeeId IN (3,4)

    GO

There are now five columns in the original table and our view is a SELECT * query - which means that SQL Server will return all columns in the table...

Right?

Errrrr... not exactly.

Try this:

    SELECT * FROM GetEmployees
  

Notice that you still only get the original four columns that were present when the view was created - you don't even see the new Country column!

Now this is unexpected by a lot of people - you see "SELECT *" and you think, "SQL Server will just return all columns in the table at runtime" but you'd be wrong.

How did this happen?

SQL Server stores what it calls "Persistent metadata" when you create (or alter) your view. You can view this metadata by using either the schema views or the catalog views - take your pick:

    SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('GetEmployees')

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='GetEmployees'

These views represent the information SQL Server has about the view - we are viewing the columns that SQL Server thinks the view exposes to the caller. When you create or alter a view, this persistent metadata is calculated and it can become out of date very easily (by adding a new column to the base table, for example).

Solution: Update the metadata!

There are a few ways to update SQL Server's metadata:

Drop and re-create the view
- Very bad - run the risk of losing all permissions!
Alter the view
- Simply running ALTER VIEW GetEmployees AS SELECT * FROM Employee would actually work!
Refreshing the view using the stored procedure sp_refreshview
- EXEC sp_refreshview GetEmployees
- My favorite technique

While all three will work, sp_refreshview is the recommended technique.

So there - now you know! And I hope that you don't forget it... Because I forget it at least once a year... And the worst part is that there's a weird thing in SQL Server regarding persistent metadata and altering tables that will sometimes (but not always) change switch around the data in the columns so that the LastName data is in the Country column...

A few common terms to help people find this article

I've seen people ask this question lots of different ways:

I added a new column to my table and it doesn't show up in my view
My view does not return all columns in my table
My SELECT * view isn't returning all of the columns in my table
Table changed but view shows old table
My view is switching column data around (this helps those who run into the situation where SQL Server will return the wrong data in the wrong column!)