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).

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.

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.

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

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

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