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

Question

Is there any way to make representations that use SELECT * remain in sync with the base table.

I found that if changes were made to the base table from which all columns should be selected, the view should be "recreated." This can be achieved simply by following the ALTER VIEW statement.

However, this can lead to some rather dangerous situations. If you forget to recreate the view, it will not return the correct data. In fact, it can return seriously corrupted data - with column names, everything is wrong and out of order.

Nothing will catch that the view is wrong, unless you were able to cover it with a test or the data integrity check failed. For example, Red Gate SQL Compare does not accept the fact that the view needs to be recreated.

To reproduce the problem, try the following:

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 PITA. Is there somewhere somewhere a setting that could fix this behavior?

Answer 1

You must stop using SELECT *. This can lead to some “rather dangerous” situations.

However, as an alternative, you can link your view layout. Thus, you cannot change the base table without re-creating the view.

Answer 2

Is there any way to make representations that use SELECT * remain in sync with the base table.

Of course: update them when updating the basic table layout :). Being serious, there is no way to automatically update views that use SELECT *, which is one of many reasons to avoid this. It would be best to explicitly list the columns in your views, and when you run the schema update scripts (are they written so that they can correctly enter the original control?), You just need to include updates in the views, if necessary.