A SQL Server view built on SELECT * can return the wrong columns
A view that has worked for years starts returning odd results after someone changes a table. A column is missing that should be there, or, worse, a column shows data that clearly belongs to a different column. Nothing errored. The view was not touched.
The view uses SELECT *.
What actually happens
When you create a view, SQL Server works out its columns once and stores that list. SELECT * is
expanded at that moment into the columns the table had at that moment. Changing the table later
does not update the view's stored list.
CREATE TABLE dbo.Product (Id int, Name varchar(50));
GO
CREATE VIEW dbo.ProductList AS SELECT * FROM dbo.Product;
GO
ALTER TABLE dbo.Product ADD Price decimal(19,4);
GO
SELECT * FROM dbo.ProductList; -- Id, Name. No Price.
Adding a column is the harmless case: it just does not appear. Removing or reordering columns is
where it goes wrong, because the view matches its stored columns to the table's columns by position.
With a join it gets worse. A view defined as SELECT a.*, b.Total FROM a JOIN b … can end up
showing a new column from a under the name Total. The column names look right and the data
under them is wrong.
Fixing a view that has drifted
sp_refreshview rebuilds a view's stored column list from its definition:
EXEC sp_refreshview N'dbo.ProductList';
To refresh every view that reads a particular table after you change it:
SELECT DISTINCT
'EXEC sp_refreshview N''' +
OBJECT_SCHEMA_NAME(d.referencing_id) + '.' + OBJECT_NAME(d.referencing_id) + ''';'
FROM sys.sql_expression_dependencies AS d
JOIN sys.views AS v ON v.object_id = d.referencing_id
WHERE d.referenced_id = OBJECT_ID(N'dbo.Product');
Run the statements it generates. For procedures and functions the equivalent is
sp_refreshsqlmodule.
Preventing it
List the columns. A view that names its columns cannot silently pick up the wrong one, and if a column it names is dropped, querying the view fails with an error that says so. That is far better than wrong data.
CREATE OR ALTER VIEW dbo.ProductList AS
SELECT Id, Name, Price FROM dbo.Product;
Or bind the view to the schema. WITH SCHEMABINDING makes SQL Server refuse any change to the
table that would affect the view until the view is changed first. It needs two-part names and does
not allow SELECT * at all, so it also enforces the first rule.
CREATE OR ALTER VIEW dbo.ProductList
WITH SCHEMABINDING
AS
SELECT Id, Name, Price FROM dbo.Product;
The cost is that every change to dbo.Product now has to drop and recreate the view around it.
For a view that other things depend on heavily, that trade is usually worth it.
Knowing which views to check
The fix is easy once you know which views read the table you are changing. That is the part people miss. In WoodFireERD, select the table and set Follow to Referenced by (others → this). The diagram shows the views, procedures and functions that read it alongside the tables that reference it, so you can see what to refresh before you make the change.