x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Reasons to not use SELECT * (and when to use it)

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)

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


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



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

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.


Print | posted on Thursday, April 22, 2004 11:00 AM | Filed Under [ SQL Server ]



# re: Reasons to not use SELECT * (and when to use it)

However, so many times (select count(*) from ... ) > 0 is much faster than exists (select * ...). even I would say "exists" executes infinity (too long). Any ideas what is happening?
4/22/2004 12:24 PM | Yuriy

# re: Reasons to not use SELECT * (and when to use it)

Another time not to use "SELECT *" - when you're databinding using autogenerated columns in .NET. Columns added to a table will automatically show up on the UI, which may not be what you want.

Also, Yurity, I've always heard you should use "SELECT COUNT(1) FROM..." rather than "SELECT COUNT (*) FROM...". That doesn't answer your question, but there you go.
4/22/2004 12:31 PM | Jon Galloway

# re: Reasons to not use SELECT * (and when to use it)

If you look at source of system SPs in master DB, you notice that MS uses if (select count(*) from sysobjects) > 0 .... instead of if exists (select * from ...).

I have noticed that after solving a problem with very poor performance in a trigger joining inserted and deleted by primary key and checking for existance of particular updates. It seems to me that if exists (...) always generates all pairs and than filters them by my criterion and by inserted.primarykey = deleted.primarykey. But, if I use if (select count(*) .. ) > 0, I get good performance. I suspect here completely different plan doing hashjoin on PK values and than filtering.

Anyway, it is not directly related to the topic.
4/22/2004 12:56 PM | Yuriy

# re: Reasons to not use SELECT * (and when to use it)

But Jeff Smith thinks its a good idea, but he's a farking idiot!
6/15/2005 12:27 AM | Joe Momma

# re: Reasons to not use SELECT * (and when to use it)

"Joe" -- that's pretty brave of you to call someone an idiot and to also do it anonymously. You must be proud ! Of course, you do know that BRett knows your IP address and I'm sure if we ask Damian nicely he will let us know which SQLTeam user it matches, right?

The saddest part about "Joe" and unfortunately some of the other things I've read here is that people still don't get it ... there is no loss of "self-documentation" if you say:

select x.*, a+b as c
from (select a,b from tbl) x

everything is completely defined in this sql statement, nothing is dependant on database object remaining unchanged, there is no security or performance penalty, and the code is clearer and shorter.

So, I'm still waiting for the "risk" to be fully explained in a SELECT * statement in that format.

Again, saying "using * in a select is bad" is very, very different than saying "using select * from a table in a select is bad".

6/15/2005 4:23 PM | Jeff

# re: Reasons to not use SELECT * (and when to use it)

Hi Brett

Another reason to avoid SELECT * -

FROM MyOtherTable

Even if the schema does not change - if you set up updateable replication then a uniqueidentifier is added to the replicated table (if one does not already exist) which can introduce bugs with the above code.

Minor one - link that suggests that SELECT * in an EXISTS() clause is not optimal (at compile time):
10/5/2006 3:31 AM | poots
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET