I’ve heard for many years that using SELECT * FROM
I’d always thought the only reason why SELECT * FROM was frowned upon was that it returned an unnecessarily large amount of data from the server, and client applications will typically want to consume only a handful of the returned columns. This opinion was changed when I came across the bug I describe in this post.
Consider the following table and view:
If we were then to run the View, we get results as expected.
Now, imagine there’s a requirement to add a new column to the table [A], somewhere in the middle of the list of columns above. For example, we want a new column called [NewCol] of type int, with default –1, between [ColB] and [ColC]. We can either make this change in SQL Server Management Studio, or else run the below script (which was generated from within SSMS).
Now, if were to execute a select on [AView], we get some rather unexpected results:
|1||ColA Val1||ColB Val1||-1|
|2||ColA Val2||ColB Val2||-1|
|3||ColA Val3||ColB Val3||-1|
|4||ColA Val4||ColB Val4||-1|
What has happened is that the view outputs the heading columns that were in place at the time the view was created, but the data is taken from the first four columns of the underlying table, which now includes the new column [NewCol] instead of [ColC].
My client application was attempting to read the column [ColC] from the returned dataset and was producing unexpected results, as it was reading data from the underlying column [NewCol].
My expectation would be that the view would either continue to return the four columns it originally did, or else return all five columns of data. Returning the original column headings but with the new data seems odd at best.
What really surprised me is that the above sample causes the problem to occur in all versions of SQL Server from at least SQL Server 2000 right through to 2008.