19 October 2007 10:54 Alex_Kuznetsov

When It Is OK to Use SELECT * in Production Code.

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.

Comments

# Pythian Group Blog &raquo; Blog Archive &raquo; Log Buffer #68: a Carnival of the Vanities for DBAs

# re: When It Is OK to Use SELECT * in Production Code.

13 December 2007 08:49 by Madhivanan

Other case to use SELECT * is when checking for existance of the data

IF EXISTS(SELECT * FROM.........)

BEGIN

-- do some stuff here

END

# re: When It Is OK to Use SELECT * in Production Code.

13 December 2007 08:50 by Madhivanan

Other case to use SELECT * is when checking for existance of the data

IF EXISTS(SELECT * FROM.........)

BEGIN

-- do some stuff here

END

# Select * question | keyongtech

22 January 2009 01:07 by Select * question | keyongtech

Pingback from  Select * question | keyongtech