Re: (Select *) vs. (Select id) from table.

Michael Trausch <michael(dot)trausch(at)comcast(dot)nope(dot)net> writes:

> Well, first, it's never really a good idea to use "SELECT * FROM" in a
> production application, against a table. Tables can (and do) change
> from one release to another, and if the layout of the table changes, you
> could be looking at having to rewrite your code, especially if it relied
> on the order of the columns in the tables.

Wouldn't it be more logical to say "it's never a good idea to depend on the
order of the columns in the tables" then?

> It's always better to specify the columns that you're looking for, since
> existing columns should (at least in theory on a production DB) remain
> present, though their order can change sometimes, depending on what the DBA
> does. :)

And then when new columns are added (or existing columns removed as you point
out) what would have to happen to your code? Hint, you used the word above...

This particular piece of dogma dates back to the days under Oracle when
"SELECT *" actively broke. If you changed the column you had to recompile the
code in question or else you got strange errors or crashes. The *only* way to
refer to columns was by position.

These days any reasonable driver allows you to use the column names to refer
to columns, and application code often has multiple levels of complexity. The
layer handling the query often has no idea which columns will be used by later
layers. If you hard code the list of columns at each layer then you have to
rewrite lots of code when you add a column or change a datatype.

--
greg