Add a new table column to specific ordinal position

Question

Is it possible to add a column to a table at a specific ordinal position in SQL Server?

For instance, our tables always have CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy columns at the "end" of each table definition? I'd like the new column to show up in SSMS above these columns.

If I am scripting all my database changes, is there a way to preserve this order at the end of the table?

FYI, I'm not trying to institute a flame war on if this should even be done. If you want to read about a thread that degenerates quickly into that, here's a good one:

http://www.developersdex.com/sql/message.asp?p=581&r=5014513

Answer 1

You have to create a temp table that mirrors the original table's schema but with the column order that you want, then copy the contents of the original to temp. Delete the original and rename the temp.

This is what SQL Management Studio does behind the scenes.

With a schema sync tool, you can generate these scripts automatically.

Answer 2

Is it really a good idea to depend on a specific ordinal position? I would recommend retrieving them by name instead of ordinal and the problem goes away and you have a more change tolerant design.

Answer 3

go into SQL Server management Studio, and "design" an existing table. Insert a column in the middle and look at the script it creates. it will basically create a temp table with the proper column order, insert the data from the original table, drop the original table, and rename the temp table. This is probably what you'll need to do.

Answer 4

To my knowledge there is no known method to change the order of the column. Behind the scenes SQL Management Studio does what Jose Basilio said. And if you have a big table then it is impractical to change the column orders like this way.

You can use a "view". With SQL views you can use any order you like without getting affected by the table column changes.

Answer 5

You cannot create a column at a specific ordinal location in the table structure - plus, why would you want to?? The order of the columns in SQL Server is totally irrelevant.

SQL Server Management Studio has a design view which seemingly allows you to create a column at any location in the list of columns - but what really happens here in the background is that the new table with the new columns gets created and the old one gets dropped.

There are no SQL DDL commands to create a column at a specific location or to "reorder" columns. It's really not needed.

Marc