Re: alter table add column - specify where the column will go?

On 2010-11-24 10.43, Thomas Kellerer wrote:
> Grzegorz Jaśkiewicz, 24.11.2010 10:37:
>> just never use SELECT *, but always call columns by names. You'll
>> avoid having to depend on the order of columns, which is never
>> guaranteed, even if the table on disk is one order, the return columns
>> could be in some other.
>>

SELECT * also makes the code harder to read since you have to lookup the
table definition to see what it'll return.

You'll also be wasting resources to handle the data you'll never use.
Maybe it doesn't matter for one resultset in one call but it quickly
adds up. (This is something ORMs usually are very bad at.)

> I always try to convince people of this as well, but when they ask me
> under which circumstances this could happen, I can't think of a proper
> example.

select * from ta join tb on ta.id=tb.aid;

Add another column to "ta" and you get a different resultset.

In general if you do any changes to your schema you need to go watch out
for code using SELECT * since it easily breaks.
For example if I do something like this in Python it will break if I add
another column:
a, b, c = resultset.next()

Had I used "SELECT a, b, c" it wouldn't. It's a lousy example but not
that uncommon.

>
> Does anybody have an example that would show this?

I still don't have an example of when the internal ordering of a tables
column could change.

Anyhow, "SELECT *" is bad practice leading to error prone code and
wasting resources.

My 2c,
/r

>
> Regards
> Thomas
>
>
>

--
Regards,
Robert "roppert" Gravsjö