Posted by Erland Sommarskog on 10/15/05 01:06

serge (sergea@nospam.ehmail.com) writes:
> Using "SELECT * " is a bad practice even
> when using a VIEW instead of a table?

Yes. SELECT * is bad in production code because:

1) The behaviour of the code may change unexpectedly when colunms are
added or dropped.
2) You cannot trace whether a column is actually used or not.
3) Unnecessary data is sent to the client.

There are a few exceptions where SELECT * is permissible:

1) In subqueries with EXISTS/NOT EXISTS of course.
2) From temp tables created in the same procedure.
3) Debug things like IF @debug = 1 SELECT * FROM #tmp.

I would also like to add that you should return columns the client acutally
uses. We are suffering from have several stored procedures where about
every column is included in the result set, and some of these procedures
returns data to clients outside our system. The problem I have, is that I
can't tell whether the column is actually there because of a requirement,
or by routine. This makes it difficult for me, if I need to change or
remove that column.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]