Bits of .NET

🗄️ Your View Got Slower the Day You Added One Column to Its Base Table

A view built with `SELECT *` (or one that references a table’s columns without an explicit list) runs noticeably slower after an unrelated new column is added to the underlying table – not because the new column is expensive to read, but because a view’s column list gets bound at CREATE time, and unless it’s refreshed, it can end up silently reading MORE data than the query actually needs.

🔎 The Problem

CREATE VIEW ActiveCustomers AS
SELECT * FROM Customers WHERE IsActive = 1;

-- Months later, someone adds a large NVARCHAR(MAX) "Notes" column to
-- Customers for an unrelated feature. The view'"'"'s metadata (sys.columns
-- for the view) still reflects the OLD column list from creation time
-- until it'"'"'s refreshed - meaning queries against the view can behave
-- inconsistently with the current table shape, and once refreshed,
-- SELECT * now pulls that large new column into every query that uses
-- the view, even ones that never needed it.

✅ Fix: Avoid SELECT * in Views, and Refresh Metadata When Schema Changes

• Rewrite the view with an explicit column list – `SELECT CustomerId, Name, Email FROM Customers WHERE IsActive = 1` – so adding unrelated columns to the base table never changes what the view returns or how much data it reads.
• After any base table schema change, run `EXEC sp_refreshview ‘ActiveCustomers’` to force the view’s cached metadata to match the table’s current definition – `SELECT *`-based views in particular can behave unpredictably with stale metadata until this is done.

⚠️ Why This Is Easy to Miss

• The person who added the new column to `Customers` often has no reason to think about `ActiveCustomers` at all – the view’s slowdown is a side effect of a change made somewhere else entirely, which is exactly what makes it hard to connect during troubleshooting.
• Explicit column lists in views are one of those practices that cost nothing when a view is created but save real debugging time months later, precisely because they decouple the view’s behavior from whatever unrelated changes happen to the base table.