SELECT * or list fields?

  • Thread starter Thread starter BruceM
  • Start date Start date
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?
 
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.
 
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.
 
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...
 
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.
Click to expand...

Remember, the rule of thumb is, never used SELECT * in _production_
code; I'm sure we all use SELECT * at some point during development.

As reagards maintenance, adding columns to the table is not so bad but
problems tend to arise when a column removed from the table or an
existing column's name or ordinal position in the schema catalog
changes (e.g. SELECT * used to spit out columns last_name then
first_name and now SELECT * expands to first_name then last_name).
Issues range from having to change code that consumes the query
regardless (i.e. you haven't saved much on maintenance) to queries
failing. As regards SQL statements ('query' is often used in the
context of access to mean a SQL statement e.g. 'DELETE query'), data
can end up being written to wrong columns and isn't spotted for some
time e.g.

INSERT INTO A
SELECT * FROM B;

Introducing risk to save some typing isn't my style.

It's worth noting the execptional cases e.g. COUNT(*) which is
overloaded to count rows rather than values, NOT EXIST (SELECT *
FROM...) which doesn't return a set but a TRUE/FALSE/UNKNOWN result.

Aside: I've been noticing DELETE * around here a lot. A while ago I
was trying to explain to a colleague, who was wondering why he was
getting a syntax error in SQL Server, that DELETE * makes no sense
because DELETE operates on rows and not columns. "What would you
expect DELETE <column_name> FROM <table_name> to do?" I enquired.
"Delete just that column," came the reply. "What do you mean, DROP the
column from the table?" I pursued. "No, just set the values to null."
I was just about enquire about NOT NULL columns when I came to my
senses and convinced him to disengage brain and simply remember the
rule of thumb, never use DELETE *.

Jamie.

--
 
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.
 
Back
Top