Posted by Erland Sommarskog on 03/21/06 16:49

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> 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.

I'm not really sure that I understand this. Why would I need a method to
specify the physical order on the page? The only time you would be able
to see this order is you do DBCC PAGE. Or are you thinking of
sys.columns.column_id? That is logical order for me.

Already today the physical order is different from logical order to some
extent. If memory serves, all-fixed length columns comes before the
variable-length columns. And a column may be entirely missing on a page,
if it was added with ALTER TABLE as nullable without a default value.

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

I should probably know this, but enlighten me. What are you thinking of?

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

There are a whole lot of features in SQL Server which have little direct
effect on end-user experience, but which make life simpler for the
developer. TRY-CATCH for instance.

And saying that column order would not be benefit to end users is probably
not very accurate. There are more sites out there than we like to know
where end users watch table data by running SELECT * or functions like
Open Table, and where the column has very direct impact on their
experience.

I like to add that there quite a few arguments for keepin a strict régime
on column order. For instance, if you want to copy data between two tables
with the same logical schema with BCP, you appreciate very much if column
order is the same, as this saves you the hassle or writing a format file.

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

The physical order is something that never have to concern me, unless
I start to read database pages directly.

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