CODEWITHSUNDEEP
sql-server
cbp
cbp

Reputation: 25628

SQL Server: Views that use SELECT * need to be recreated if the underlying table changes

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?

Upvotes: 4

Views: 1716

Answers (2)

Thomas
Thomas

Reputation: 64645

Is there a way to make views that use SELECT * stay in sync with the underlying table.

Certainly: Update them when you update the underlying table schema :). Being serious, there is no means to automatically update views that use SELECT * which is one of the many reasons to avoid it. A better way would be to explicitly enumerate the columns in your views and when you run your schema update scripts (they are scripted so they can go into source control right?) you simply need to include updates to the views if necessary.

Upvotes: 0

Robin Day
Robin Day

Reputation: 102488

You should stop using SELECT *. It can always lead to some "pretty dangerous" situations.

However, as an alternative, you can make your views schema bound. That way you won't be able to change the underlying table without re-creating the view.

Upvotes: 9

Related Questions