Posted by Anith Sen on 03/21/06 01:34

>> How do you know that?

Anyone with familiarity with data management fundamentals would know for a
fact that assigning positional significance to columns in a relational table
is wasteful.

The header of a relational table is a set of typed column names and elements
of a set have no order. Imposing ordering to the elements is pointless since
they are already identifiable by name. Moreover such ordering significance
when introduced in queries would make them more complex, since the users are
forced to remember the column order along with the column name with no real
advantage.

>> If Surya says he wants to add a column in a place in the table, it is
>> probably because that this is his requirement.

If Surya is aware of the fact that columns are identified by column names,
and positional arrangement of columns provides only superficial aesthetic
benefits, he would not have asked this in the first place.

>> There are several reasons why you want to add new column anywhere in the
>> column list. We frequently add columns (and drop) to our tables, and it
>> would be a complete mess, if column order was historic.

Some may lack the discipline to formalize all the schema changes and/or
document them appropriately. But that does not mean everyone is facing such
mess. Of course you don't expect everyone to be in one, do you :-)?

>> I try arrange columns in a logical order, so that it's easier to read the
>> database documentation, so it's easier to view data with "SELECT *",
>> which we use a lot when looking at data from Query Analyzer.

It is the tail wagging the dog. Your documentation should follow the schema,
not the other way around. If you have poor documentation practices, consider
rectifying it by using better source code configuration/ management
protocols. "Easy to read the doc" is not really a reason to add something
that can be superfluous.

>> This is a very good feature that is missing from SQL Server,...

Not sure if many had found it missing, but as for a DDL at the logical level
it is as simple as shortcut that transparently drops & recreates the table.
There are considerable physical model implications with changing column
order, esp. if there are clustered indexes on the columns that are affected
by the change. Even if it is a heap, any underlying implementations of
constraints, defaults or rule would have to be bound differently if the
columns changed are somehow affected by them.

Having said all the above, I know that SQL deviates from relational model to
some extent. And column order is inherently a part of SQL and is ingrained
in so many aspects of the language as a whole. While positional significance
of the columns in SQL is seemingly relevant esp. with metadata & catalog
management, as a good development practice, it is better to avoid relying on
column order in data definition, manipulation and data management in
general.

--
Anith

[Back to original message]