If ever a DBA walked up a mountain and came back down with two stones that had 10 commandments written on them, “thou shalt not use SELECT *” would be one of them. However, that same DBA would turn around and within 5 minutes use it themselves!

One place that DBAs use SELECT * is when they create views. The idea is that you do actually want every column available to the view because the query that hits that view should limit which columns it wants returned.

This works, but there is one big issue with it that most people learn the hard way. If the schema of the source table that the view is selecting from changes, the view will NOT automatically update to include those changes.

For example, let’s create a simple table with some data and a view over it (because we are using really simple code, I’m not going format visually the way I normally do).

CREATE TABLE tblViewExample (col1 INT NULL);
GO
CREATE VIEW vViewExample AS SELECT * FROM tblViewExample
GO
INSERT INTO vViewExample (col1) VALUES (1);
INSERT INTO vViewExample (col1) VALUES (2);
INSERT INTO vViewExample (col1) VALUES (3);
GO
SELECT * FROM vViewExample;

I did break the rule of no SELECT * again but it’s to demonstrate the point of this article.

So far everything is being returned as we expect it. Now let’s change the schema of our table and see what happens.

ALTER TABLE tblViewExample ADD col2 INT NULL;
GO
UPDATE tblViewExample SET col2 = col1 * 5;
GO
SELECT * FROM vViewExample;

Notice that col2 is missing even though the view is using SELECT *. Let’s see what happens if we try to use the view to insert a new record with data in col2.

INSERT INTO vViewExample (col1, col2) VALUES (5, 5);

To fix the “broken” view, we have to rebuild it.

DROP VIEW vViewExample;
GO
CREATE VIEW vViewExample AS SELECT * FROM tblViewExample

Now let’s try the insert statement again and then the select statement.

INSERT INTO vViewExample (col1, col2) VALUES (5, 5);

SELECT * FROM vViewExample;

And of course, let’s clean up after ourselves!

DROP VIEW vViewExample;
DROP TABLE tblViewExample;