|







|
 |



These are my top 6 reasons why you should not use SELECT * in you SQL statements. Instead you should
enumerate all the fields that you want to select. There are probably more reasons but I couldn't think of any more
when writing this.
- By selecting only the fields that you want you don't return any superfluous fields. This results in an
immediate speed increase. For example consider the following table:

Suppose we wish to create a drop down select box. To do this we really only need to the information from the first two fields
(CustomerID and CustomerName). However, if we use SELECT * then we are pulling all the information for each customer
from the database as we write each record, even if we don't need it (ie we are pulling data in the CustomerAddress and
CustomerComments fields even though we don't need this information). By only selecting the fields we need we reduce the amount
of data pulled from the database and thus speed up our application.
- At some point in time you'll want to look at using the Recordset Object's .getRows() method to
convert a recordset into an array and close your recordset objects earlier. However you will not be able to do this
effectively unless you know which fields correspond to which array elements (which requires you to enumerate
the fields in the SELECT statement).
- At some point you will also look at using the Recordset Object's .getString() method which is even faster
than the .getRows() method for returning recordsets to the screen. If you use SELECT * you will have no
control over the order in which columns are displayed on the screen.
- If you have Access Memo type fields, or SQL Server Text type fields these need to be selected last in your
SQL statement, otherwise you will start to run into the problem where these records either do not appear on the
screen, or are truncated
(see Microsoft's KB article: Q200124).
- SELECT * is lazy coding practise. It's probably best to start with good habits early. As well the old saying of
"a stitch in time saves nine" is very true. Imagine having to trawl through a 1000 line ASP page that you
coded 6 months ago trying to find all the fields you used to edit a SELECT statement at the top of the page. Then
imagine doing this for 1000 pages! Better to do it right the first time.
- This last point is not a fact, but merely supposition at this point in time. I've heard that if you do SELECT * the database
needs to find out what fields are actually in the table before it can then select them all. By specifying the field names the database engine
can use those names straight away rather than having to do an extra lookup. However there is very little evidence on the web to say
one way or the other.
Back to FAQ listing.
|
 |
 |
 |
 |
 |
 |
 |
 |
 |
| |