SELECT * or list fields?

B

BruceM

I have heard that a listing of all fields instead of SELECT * should be used
in queries, but then I have seen SELECT * used by people I would regard as
experts, so I don't know what to think. What if any are the potential
problems with SELECT * that would offset its convenience? If there is
consensus one way or the other with regard to queries, does the same
consensus apply to SQL that is used in VBA?
 
J

Jerry Whittle

There are a couple of problems with SELECT SPLAT.

1. You can get too use to it and bring over fields that aren't needed. This
can be a performance hit over networks.

2. Consider the following:

INSERT INTO Asa2
SELECT Asa.*
FROM Asa;

Guess what happens if someone adds or deletes a column in either the Asa or
Asa2 tables?

On the flip side if you add a column and use the splat, it will bring over
the data without thinking about it.

I must admit to using the splat on databases where I have full control.
However in a multi-developer environment, such as at my day job, our rule is
to add all the fields.
 
B

BruceM

Thanks for the reply. In general I list the fields because it is clearer to
have the list in front of me, but sometimes, especially during development
when the list of fields may change, SELECT * offers convenience. I don't
intend to make it my general practice, but it sounds as if I can use it with
discretion if I so choose in my single-developer environment.
 
D

Duane Hookom

One other major issue with SELECT * is when more than one table is included
and field names are not unique. You could end up with multiple fields in you
output named "EmployeeID" or "OrderID". I hate when that happens...
 
B

BruceM

Thanks to all for the replies. I have indeed noticed the problems with two
fields (typically related fields in two tables) having the same name when
SELECT * is used in a query. The thing that prompted my question was row
source SQL I used in a form's Current event procedure. I had listed all of
the fields, then decided I needed another field in the table, and it took me
a while to sort out the #Name error when I attempted to add to the form a
control bound to the new field. There were a few other anomalies, too. I
realized I could have avoided the problem with SELECT *, although I had more
or less decided a while ago to list the fields as a general rule, so I
decided to ask about it. Everything I have heard confirms that the bit of
extra typing is well worth the effort in most cases.
 
Top