Alex_Kuznetsov

If you google up 'never use SELECT * in production code' you will get many many hits.
Clearly in many cases using SELECT * makes your code vulnerable to changes in underlying table(s) and as such should be avoided.
Yet I don't think the common rule of thumb 'never use SELECT * in production code' should be used
without thinking, I don't think it should be blindly applied in all the situations.
For instanse, consider a request to display top five sales for every region and a simple query that satisfies it

SELECT SalesAmount, RegionName, LongListOfOtherColumns
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
  ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

Does repeating SalesAmount, RegionName, LongListOfOtherColumns twice make your code nay better/safer/readable?
I don't think so. Consider the following alternative:

SELECT *
FROM(
SELECT SalesAmount, RegionName, LongListOfOtherColumns,
  ROW_NUMBER() OVER(PARTITION BY RegionName ORDER BY SalesAmount DESC) AS rn
FROM Sales.Sales
) t WHERE rn < 6

It is shorter, easier to maintain, and just as robust as the original query.
You have explicitly listed the columns
in your subquery, so you are already insulated from any changes in Sales.Sales table.
You don't need an additional layer of protection.
Repeating the list of columns twice only makes your code more prone to errors.