Adding column for the Alter Table cmd

[batteam]

Does anyone know if its possible to specify the actual physical placement of a new column when adding a new one to an existing table? We are working on a production server thus do not have permission to modify tables, only submit SQL scripts to have our dba modify the objects.

Currently, SQL automatically places the column physically as the last column in the table. I would like to specify where in respect to the existing columns the new one would be.

For example, I use the commands:
Alter Table dbo.Emp_Data
Add
middle_initial char(1), ??
go

Instead of having this new column put at the very end of the Emp_Data table, I would like to put it in between the first and last name columns. Thanks for any help you can provide.

 

[SQLDenis]

you have to create a new table, copy all the data into it, rename the old table, rename the new table (same as the old one) and drop the old table....all within a transaction of course

This is what enterprise manager does behind the scenes


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[batteam]

Ok, thanks for your help.

 

[SQLSister]

It is truly a very poor idea to try to move columns around. You are creating alot of extra load on the server when you do this and it is utterly unneeded. Since you should always specify the columns you want in the order you want them when selecting data, there is no need to have columns in a table in a specific order.

"NOTHING is more important in a database than integrity." ESquared

 

[batteam]

Thanks for the reply.