I'd like to expand this list with anyones comments. Thanks! And if anyone knows the M$ link that discusses how they optimized SELECT * for existance, I'd appreciate a linke. Thamks again!
Why you shouldn't
1. Effeciency: Only the data you need
Reduces the amount of I/O that has to occur
2. Better use of indexes
May use Index intersection or make use of covered indexes
3. Isolate code from Table object changes
For example, the following would through an error
INSERT INTO myTable99(Col1, Col2, Col3,ect)
SELECT * FROM myTable00 WHERE
4. I get a chuckle when Access developers say DELETE * FROM mytable99
'nough said
5. Increases network traffic, requires more buffers and processing
Similar to Item #1
6. Constantly accessing the system tables to figure out what these columns are.
7. Because Miracles happen
http://www.dbforums.com/t996606.html
8. When a VIEW is Created a SELECT * , and the Table is altered or dropped and recreated, the view will refernce the old table structure as ehorn points out
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35975
When to Use SELECT *
In some instances, it is more effecient to use SELECT *.
1. In a coorelated query, SELECT * is actually optimized
SELECT Col1, Col2, Col3 FROM myTable99 a
WHERE EXISTS (SELECT * FROM myTable00 b WHERE a.id = b.id)
2. In an existence check as well
IF EXISTS(SELECT * FROM myTable99)
3. EDIT: Actually, Jeff Smith (aka Dr. Cross Join) makes a very valid point in this link. Let's say that you are making a derived table, especially when there may be many derived tables that are nested, what then would be the harm in passing up the columns using SELECT * ? My only downside is that developers not used to correct coding methods may adopt this style in ways that are not good. Also this is not to say that SELECT * is appropriate for this intial derived table, which would again be selecting way more than you need.