Alter table and column order

Is it possible to add a column to a table using the "alter table"
statement and specify where in the sequence of columns the new column
sits. If not is there any way to alter the order of columns using TSQL
rather than Enterprise Manager / Design Table.

TIA

Laurence BreezeNo and no.

The physical column order should only be significant when you use SELECT * -
and you shouldn't use SELECT * in production code. List the columns in your
SELECT statements in whatever order you want them. Alternatively, create a
view over the table with the columns in the required order.

Your other option is to create a new table, populate it from the original
table and then rename it. This is what Enterprise Manager does behind the
scenes.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> No and no.
> The physical column order should only be significant when you use SELECT
> * - and you shouldn't use SELECT * in production code. List the columns
> in your SELECT statements in whatever order you want them.

However, in support or debug situations, SELECT * is very convenient to
use. And in this case, I find it important that columns are in some
reasonable order. And historic order is rarely reasonable.

Furthermore, if you want to move data between databases, it is far
simpler if columns are in the same order in both databases, as this
makes bulk-copying easier.

One should also not ignore the documenation aspect of it. When you read
the documentation of a 50-column table, do you prefer to have the columns
in historic order, or do you prefer some logic order with the primary
key first, and related columns close to each other.

So, while there is no support to insert a column in the middle other
than creating a new table and move data over, it is certainly a valid
question. Column order does matter!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp