Microsoft SQL Server view not showing the correct results

There is a lot of post and signals that we shouldn’t use the “SELECT *” in the production code. You can easily find a good articles: here is one of them. I would like to wirte about one more things which is really dangerous, but very rarely reported. The problem may couse that the view is not returning the correct data and there is no errors – what worse can happen to us?

At the beginning let’s create new simple table and insert some sample data.

Than create the view as a select *.

And see the results:

As we can see, the results from table and view are corrent and returning the same values.
1

Now, let’s add new data and see what happen.

We can check the results and see that everything works just fine.
2

As for now everything was working good, but let’s do some more fun.
We are going to alter the table and see what happen. FIrst of all we are going to add new column.

And when we check the results we will see:
3

And… as we can see: there is a different between the original table and the view… But lest’s go further and update the new column – maybe it helps to solve the problem.

Unfortunatelly, the results from view are just incorrect.
4

But there is something even wors. Now we just can’t see something new from our table, but the data returned from view are correct – incomplete, but correct, but see what happen if we will drop one of the column…

And now the results are:
7

As you can see the SQL Server return the data from column: age in the column called StudentName. It’s really dangerous and can couse a lot of problems in production code. What is the solution? Solution is simple: never use a “SELECT *” in your production code (of course there is a few exceptions, but generally you shouldn’t).

But if you would like to repair the results in the view, you can use:

And now the results are correct:
8

Leave a Comment

Your email address will not be published. Required fields are marked *