🗄️ SQL Server: Fix a View Returning Stale Data After a Schema Change
A view defined with SELECT * doesn’t re-derive its column list on every query — SQL Server captures that list once, at the time the view is created or last refreshed, and keeps using it. Add a new column to the underlying table afterward, and the view keeps returning exactly the columns it had before, silently, with no error pointing out that the view’s cached metadata and the table’s actual current schema have drifted apart.
🔎 The Problem
CREATE VIEW dbo.ActiveCustomers AS SELECT * FROM dbo.Customers WHERE IsActive = 1; -- view's cached column list: CustomerId, Name, IsActive ALTER TABLE dbo.Customers ADD LoyaltyTier VARCHAR(20) NULL; SELECT * FROM dbo.ActiveCustomers; -- still returns only CustomerId, Name, IsActive - -- LoyaltyTier is invisible through the view until it's refreshed
✅ Fix: Refresh the View’s Metadata, or Stop Hiding the Column List
- Run
EXEC sp_refreshview 'dbo.ActiveCustomers';right after any schema change to a table that feeds a SELECT * view, so the view’s cached column metadata catches up with the table’s actual current columns. - Make refreshing dependent views a standard step in your migration scripts whenever a base table changes, rather than something someone has to remember to do manually after the fact.
- Rewrite views to list columns explicitly instead of using SELECT * wherever practical; an explicit list makes a new column’s absence from the view a deliberate choice instead of an invisible side effect, and makes a removed column fail loudly instead of silently.
🚨 Why This Is Dangerous
- A report or application built on top of a view that’s quietly missing a newly added column can go a long time without anyone noticing, since the query still runs successfully and returns a result — just not the complete one anyone expects after the schema change.
SELECT * in a view looks like it always reflects the table underneath it, but what it actually reflects is the table as it looked the moment the view last had its metadata refreshed.
