Posted by David Portas on 03/21/06 15:04

Erland Sommarskog wrote:
> > 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.
>
> First you said that column does not matter, because we address columns
> by name and position, and now you says it matters a whole because there
> may be a poor implementation?

Regretably standard SQL requires column ordering in tables. The world
isn't going to solve that problem any time soon. Unfortunately SQL
Server exacerbates the problem by making no distinction between
physical and logical ordering of columns. I think this is the problem
that Anith is referring to.

If we are to have a new feature for fixing logical column order then I
think we must have an independent method to specify physical order as
well. Physical order should not follow logical order. Separating the
two would remove the requirement to rebuild physical structures when
some logical user requirement changes.

Unfortunately, making it easier to re-order columns will also show up
some weaknesses in SQL Server features. I'm talking about the
proprietary features that are not invariant to column order. I don't
even know a complete list of those problem points. If we want Microsoft
to support logical column ordering for apparently "cosmetic"
documentation purposes (in other words for things that shouldn't affect
end user functionality) then we'd better be very sure which features
are to be defined over logical column order and which over physical
order. That is not a trivial issue and it is the reason why I'm glad
that for the moment re-ordering columns is "hard" to do.

--
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]