What's a good use case for SELECT * in production code?

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later. Your case does look like a fair use of it though.

The place where I find SELECT * to be a must - and its evil cousin "INSERT INTO tbl" without a column list - is in an archiving situation, where rows are being moved to another table that must have the same structure.

INSERT INTO SalesOrderArchive  -- Note no column list
SELECT *
  FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

DELETE FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

If a new column is added to SalesOrder in the future, but not to SalesOrderArchive, the INSERT will fail. Which sounds bad, but it's actually a really good thing! Because the alternative is much worse. If all the columns were listed on the INSERT and the SELECT, then the INSERT would succeed, and so would the following DELETE (which is effectively "DELETE *"). Production code that succeeds doesn't get any attention, and it may be a long time before someone notices that the new column is not being archived, but being silently deleted altogether.

What’s a good use of select * in production?

IMO only things like this:

eg

create table #foo(a int, b int, c int, d int)
...
select * from #foo

or

with q as
(
  select a, b, c
  from ...
)
select *
from q

ie when the * is bound to an explicit column list that's declared in the same batch, and is used just to avoid repeating the column list multiple times.

When the * refers to a table or view, there are some nasty complexities about cached metadata, eg here. And it doesn't really save you typing, as SSMS will allow you to drag-and-drop the complete column list.

A valid use of SELECT * occurs when introduced by EXISTS.

WHERE EXISTS (SELECT * FROM ...

Like all uses of SELECT *, this has the unwelcome (and unnecessary here) side-effect of preventing WITH SCHEMABINDING from being added if the code is inside e.g. a function.

Related Q & A: EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?

See also:

• Bad habits to kick : using SELECT * / omitting the column list by Aaron Bertrand
• When Select * Doesn’t Matter by Erik Darling.