MSSQL: Views that use SELECT * need to be recreated if the underlying table changes
Posted
by cbp
on Stack Overflow
See other posts from Stack Overflow
or by cbp
Published on 2010-04-27T07:27:23Z
Indexed on
2010/04/27
7:33 UTC
Read the original article
Hit count: 266
mssql
Is there a way to make views that use SELECT * stay in sync with the underlying table.
What I have discovered is that if changes are made to the underlying table, from which all columns are to be selected, the view needs to be 'recreated'. This can be achieved simly by running an ALTER VIEW statement.
However this can lead to some pretty dangerous situations. If you forgot to recreate the view, it will not be returning the correct data. In fact it can be returning seriously messed up data - with the names of the columns all wrong and out of order.
Nothing will pick up that the view is wrong unless you happened to have it covered by a test, or a data integrity check fails. For example, Red Gate SQL Compare doesn't pick up the fact that the view needs to be recreated.
To replicate the problem, try these statements:
CREATE TABLE Foobar (Bar varchar(20))
CREATE VIEW v_Foobar AS SELECT * FROM Foobar
INSERT INTO Foobar (Bar) VALUES ('Hi there')
SELECT * FROM v_Foobar
ALTER TABLE Foobar
ADD Baz varchar(20)
SELECT * FROM v_Foobar
DROP VIEW v_Foobar
DROP TABLE Foobar
I am tempted to stop using SELECT * in views, which will be a PITA. Is there a setting somewhere perhaps that could fix this behaviour?
© Stack Overflow or respective owner