Add Column on SQL Server on Specific Place?

Question

Hello.

I would like to know if there's a way to add a column to an SQL Server table after it's created and in a specific position??

Thanks.

Answer 1

With Sql Server Management Studio you can open the table in design and drag and drop the column wherever you want

Answer 2

As Kane says, it's not possible in a direct way. You can see how Management Studio does it by adding a column in the design mode and checking out the change script.

If the column is not in the last position, the script basically drops the table and recreates it, with the new column in the desired position.

Answer 3

The safest way to do this is.

• Create your new table with the correct column order
• Copy the data from the old table.
• Drop the Old Table.

Answer 4

The only safe way of doing that is creating a new table (with the column where you want it), migrating the data, dropping the original table, and renaming the new table to the original name.

This is what Management Studio does for you when you insert columns.

Answer 5

You can do that in Management-Studio. You can examine the way this is accomplished by generating the SQL-script BEFORE saving the change. Basically it's achieved by:

• removing all foreign keys
• creating a new table with the added column
• copying all data from the old into the new table
• dropping the old table
• renaming the new table to the old name
• recreating all the foreign keys

Answer 6

No.

Basically, SSMS behind the scenes will copy the table, constraints, etc, drop the old table and rename the new.

The reason is simple - columns are not meant to be ordered (nor are rows), so you're always meant to list which columns you want in a result set (select * is a bit of a hack)

Answer 7

In databases table columns don't have order.

Write proper select statement and create a view

Answer 8

In addition to all the other responses, remember that you can reorder and rename columns in VIEWs. So, if you find it necessary to store the data in one format but present it in another, you can simply add the column on to the end of the table and create a single table view that reorders and renames the columns you want to show. In almost every circumstance, this view will behave exactly like the original table.

Answer 9

As others have pointed out you can do this by creating a temp table moving the data and droping the orginal table and then renaming the other table. This is stupid thing to do though. If your table is large, it could be very time-consuming to do this and users will be locked out during the process. This issomething you NEVER want to do to any table in production.

There is absolutely no reason to ever care what order the columns are in a table since you should not be relying on column order anyway (what if someone else did this same stupid thing?). No queries should use select * or ordinal positions to get columns. If you are doing this now, this is broken code and needs to be fixed immediately as the results are not always going to be as expected. For instance if you do insert a column where you want it and someone else is using select * for a report, suddenly the partnumber is showing up in the spot that used to hold the Price.

By doing what you want to do, you may break much more than you fix by putting the column where you personally want it. Column order in tables should always be irrelevant. You should not be doing this every time you want columns to appear in a differnt order.