Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a View Returning Stale Data After a Schema Change

- 18.11.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

Average of all values in a column that are not zero in SQL

Average of all values in a column that are not zero in SQL

List all Foreign Keys referencing a given table in SQL Server

List all Foreign Keys referencing a given table in SQL Server

ORDER BY with CASE and UNION in SQL Server

"ORDER BY" with CASE and UNION in SQL Server

Views: 2

Post navigation

CSS: Fix an aspect-ratio Box Collapsing When Content Overflows
Photoshop: Fix the History Brush Reverting Past a Resize Step

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,056)
  • Get the User Name and Domain Name from an Email Address in SQL (982)
  • How to add default value for Entity Framework migrations for DateTime and Bool (980)
  • Get the First and Last Word from a String or Sentence in SQL (859)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (657)
  • Add Constraint to SQL Table to ensure email contains @ (612)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (548)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • HTML5: Fix a datalist Suggestion List Not Filtering as You Type
  • WordPress: Fix a Custom Field Resetting After Every Autosave
  • Docker: Fix a Healthcheck Marking a Container Unhealthy Too Soon
  • Windows 11: Fix Voice Typing Placing Punctuation in the Wrong Spot
  • Git: Fix a Stash Pop Conflicting With Unrelated Untracked Files
  • Photoshop: Fix the History Brush Reverting Past a Resize Step
  • SQL Server: Fix a View Returning Stale Data After a Schema Change
  • CSS: Fix an aspect-ratio Box Collapsing When Content Overflows
  • Photoshop: Fix the Color Picker Clamping a Custom Swatch’s Hue
  • JavaScript: Fix Array.sort Reordering Equal Items Unexpectedly

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,056)
  • Get the User Name and Domain Name from an Email Address in SQL (982)
  • How to add default value for Entity Framework migrations for DateTime and Bool (980)
  • Get the First and Last Word from a String or Sentence in SQL (859)
  • How to select distinct rows in a datatable in C# (856)

Recent Posts

  • HTML5: Fix a datalist Suggestion List Not Filtering as You Type
  • WordPress: Fix a Custom Field Resetting After Every Autosave
  • Docker: Fix a Healthcheck Marking a Container Unhealthy Too Soon
  • Windows 11: Fix Voice Typing Placing Punctuation in the Wrong Spot
  • Git: Fix a Stash Pop Conflicting With Unrelated Untracked Files

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com