Avoid broken Views in SQL Server with Schemabinding
SQL Server views can get really weird when an underlying table is updated. I’ll show some examples and also the one must do for views to avoid problems.
The broken views problem is something I discovered when working on a production system. When I first observed it I was shocked. When I dug deeper into it I was even more shocked at the pure evilness this behaviour could cause. But let’s take it from the start with a simple table of colours and a view on top of it to start with.
CREATE TABLE [dbo].[Colours] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Colour] NVARCHAR (10) NOT NULL, CONSTRAINT [PK_Colours] PRIMARY KEY CLUSTERED ([Id] ASC) ); GO CREATE VIEW [dbo].[BadView] AS SELECT * FROM Colours |
Populate the table with some data and test the view.
SELECT * FROM BadView |
Id Colour ----------- ---------- 1 Red 2 Green 3 Blue 4 White 5 Black
Now, let’s break havoc to the view. I’ll recreate the table with another column RGB which is inserted right after the Id column. The Colour column is renamed to ColourName. Before running the next test, I’ve inserted the appropriate RGB values in the new column.
SELECT * FROM BadView |
Id Colour ----------- ------ 1 FF0000 2 00FF00 3 0000FF 4 FFFFFF 5 000000
This is what I consider weird behaviour. The output of the view still contains two columns with the names Id and Colour. That would be good – if it contained the right data. It doesn’t. The data is taken from the second column of the underlying table. If you’re not scared enough by this I have some more, even worse things to show…
