It is well known that SELECT * is not acceptable in production code, with the exception of this pattern:
IF EXISTS(SELECT *
We all know that whenever we see code code like this:
Listing 1. "Bad" SQL
SELECT Column1 ,
Column2 ,
Column3
,
Column4
,
Column5
,
Column6
FROM ( SELECT c.* ,
ROW_NUMBER() OVER ( PARTITION BY Column1 ORDER BY Column2 ) AS rn
FROM data.SomeTable AS c
) AS c
WHERE rn < 5
we are supposed to automatically replace * with an explicit list of columns, as follows:
Listing 2. "Good" SQL
SELECT Column1 ,
Column2 ,
Column3 ,
Column4 ,
Column5 ,
Column6
FROM (
SELECT Column1 ,
Column2 ,
Column3 ,
Column4 ,
Column5 ,
Column6 ,
ROW_NUMBER() OVER ( PARTITION BY Column1
ORDER BY Column2 ) AS rn
FROM data.SomeTable
) AS c
WHERE rn < 5
However, repeating one and the same list of columns twice is not the best practice in object-oriented programming. Why should it be the best practice in database programming?
Of course, as we all know, when we list columns explicitly, we protect our code against changes in the underlying objects. However, I cannot come up with a situation when Listing 2 is any safer than Listing 1. Apparently it is enough to list columns explicitly just once.
For all practical purposes, Listing 1 seems to be just as safe as Listing 2. If this is the case, if I am not missing anything, then SELECT * in this particular case, in Listing 1, is completely acceptable, because replacing it with an explicit list of columns does not improve anything.
More to the point, Listing 1 is shorter and more readable, and as such should be preferable.
What do you think?