|
|
Posted by David Portas on 03/21/06 20:13
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).
CREATE TABLE dbo.t1 (col1 INT, col2 INT, col3 INT);
ALTER TABLE dbo.t1 DROP COLUMN col2;
SELECT name, column_id
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.t1');
Result:
name column_id
------ -----------
col1 1
col3 3
(2 row(s) affected)
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.
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.
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? I don't see how. So
I guess I have to resort to RESTORE DATABASE for that one. No worse
than the current situation maybe, but now that nice easy syntax looks
like something that needs to be well protected from tampering and used
with almost as much reluctance as you would drop and recreate the
table.
Column ordering cannot be seen as something that has minimal production
impact under the current state of SQL Server. So I'd say there's a lot
of other stuff to fix with a higher priority than modifying column
order. And maybe if a few of those things were fixed then we'd care
less about column order anyway.
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
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
[Back to original message]
|