Posted by Erland Sommarskog on 03/20/06 13:50

Jens (Jens@sqlserver2005.de) writes:
> there is no need to put the columns in a table in a correct order,

How do you know that? If Surya says he wants to add a column in a place
in the table, it is probably because that this is his requirement.

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

> There is further no way to put a column "in between" because there is
> no need to.

Crap. This is a very good feature that is missing from SQL Server, and
if you see the link that Razvan posted, you find that that suggestion
has attracted quite a few votes.

> If you really want to do this (but I would suggest you not
> to do this) you have to recreate the table with the new column in
> between and copy alle the data from the old table to the new table.

Yes, that is what we do today. ALTER TABLE as it is today is essentially
worthless; it can be used only exceptionally.

The important thing to keep in mind when doing this is to remember to
recreate indexes, triggers and constraints. And to move referencing
foreign keys.


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