Posted by Erland Sommarskog on 03/21/06 23:45

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> I did actually think that column_id matched physical order. Maybe I'm
> wrong about the ordering on the page, but at the very least SQL Server
> does maintain the value of column_id separate from the column position
> displayed in the GUI. In the following example the order is preserved
> but the value of column_id doesn't match what is displayed by SELECT *
> or the information_schema (3 instead of 2).

I would say that column_id reflects logical order, although the gap is a
bit confusing. I guess it has to do with cascading updates to other
system tables.

By the way, find a table with a mix of columns on SQL 2000 and look at
offset and xoffset in syscolumns for that table. Not that I know whether
that information means anything.

> You are probably right that physical order is unimportant today but if
> SQL Server ever gets a wider range of options for physical data
> structures it may become relevant. Then we'll need a separate syntax
> for physical order perhaps.

Ouch! I rather not think of it. :-)

> Below is a list of some features that are sensitive to column order in
> SQL Server 2005. This is just TSQL. There are others in SSIS and
> possibly some in AS and elsewhere too. Of course it's easy to say that
> these should respect whatever logical order is defined by DDL
> statements - I assume they will. The point is that re-ordering columns
> can break a lot of legacy code. Admittedly it's sloppy code that would
> break in most cases but it is documented behaviour nevertheless.

Interesting, sloppy code is one reason why you may want to do this. Say
that that the code is littered with SELECT * and on top of that
client code refers to columns by numeric index, not by name.

As long as you only have one database, ALTER TABLE may be good. But
assume that you have several databases, for instance because the
application is a product sold to customers. If you rely on ALTER TABLE
of today, and a an update script is run out of order or some other
accident occurs at one site, you get chaos. If you can specify where to
insert a new column, you have a little more control. (This is not one
of the stronger arguments, though. You could say that that application
design deserves chaos.)

> Now suppose changing the column order does break something that depends
> on column_id (COLUMNS_UPDATED for example). Can I always recover the
> prior state as easily as I screwed it up? Take your BEFORE and AFTER
> suggestion for example. Would that allow me to move col3 to position 2
> and at the same time give it a column_id of 3 or 4?

columns_updated might be a case. But this feature appears to hopelessly
difficult to use, that I have always stayed away from it.

Then again, since this feature depends so strongly on columns, say
that you added two columns to a table in the wrong order, and you
did not discover until two weeks later. Admit that an ALTER TABLE MOVE
COLUMN would come in handy here! :-)

> SELECT
> SELECT INTO
> INSERT
> CREATE VIEW (with SELECT *)
> UNION
> CHECKSUM(*)
> BINARY_CHECKSUM(*)
> COLUMNS_UPDATED()
> WITH
> OUTPUT
> sys.columns.column_id
> information_schema.ordinal_position
> BCP
> BULK INSERT

Of these checksum and binary_checksum could possibly to depend on physical
order to some extent, but it's doubtful whether it is a good idea to
compare values from two databases.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]