adOpenStatic Logo
Navigation
Home
Resources
ASPMSAccess FAQ
Contact Ken
Copyright 2000 -




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.
  1. 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:

    Example 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.

  2. 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).

  3. 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.

  4. 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).

  5. 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.

  6. 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.