Why can't I reorder my SQL Server columns?

I have an old table with FKs in it. I want to add a new column. I'll want to make this new column my primary key.开发者_开发技巧 So I thought I could either insert that column as the first one or insert it at the end of my table and re-order my columns afterwards.

But SQL Server Management Studio did not allow me to do that. I understand that I cannot do that, and that column order is almost completely irrelevant in SQL.

What I want to know, is how come... I mean, I can drop a column... isn't that the same thing as adding a new one?

I'm just trying to understand what's going on. I've had a hard time finding documentation for that also. If anyone can point me in the good direction.

---

Definitely you can. Uncheck this option in SQL Server Management Studio:
Tools > Options > Designers > Prevent saving changes that require table re-creation.

Please don't do this in production unless you know the implications!

---

Reordering columns is basically the same as remaking the table, which is the workaround if changes that require recreation are disabled:

SELECT (fields in DESIRED order)
INTO MyNewTable
FROM OldTable

---

You might be interested in the answers to this question.

As to why you can't reorder columns (aside from the SSMS-specific answers posted), I'm not sure you'll find a canonical answer, other than the SQL standard contains no programmatic syntax for doing so - the only way to redefine a table's schema (including the order of its columns) is to drop the table and recreate it. So any management application that provides column "reordering" will most likely be doing that behind the scenes (e.g. SSMS's Design View, as pointed out in the linked question).

Someone with deeper knowledge of the SQL specification could contradict this if there is an explicit reason for not providing a column-reordering mechanism as part of the standard DDL, but I can only conjecture that, since queries define the order of columns in their resultsets anyway, and DBMSes control physical storage according to their individual implementation, that logical column order is essentially meaningless and doesn't warrant such a mechanism.

---

If my suspicion is correct you have a setting turned on that you need to turn off to allow changes that require a table to be re-created to be saved.

Open SQL Management Studio and use the menu to go to Tools->Options

In the options menu on the left select Designers

In the Table Options pane on the right ensure that the check box next to the setting "Prevent saving changes that require table re-creation" is unchecked.

After you confirm that is done try reordering your columns again.

---

What I want to know, is how come... I mean, I can drop a column... isn't that the same thing as adding a new one?

Dropping a column is based on column name, not ordinal position (first/second/third/etc column). Adding a column is always appended to the end of the list of columns. This reinforces that the sequence of columns is completely irrelevant to data besides SQL operations. I don't know how you see either as being similar...

No SQL databases I'm aware of support inserting columns in a particular order into an existing table. PHPMyAdmin provided functionality, but under the covers the function is creating a new table with the desired column order, copying the data from the old to new table, and finally deleting/dropping the old table.

Column order provides no benefit to operations...

---

You do NOT want to do that unless the table has no data and is NOT on production! The reason is that it has to take the existing records out to a temp table, delete the old table, rename the temp table. This is a very bad thing to do to an existing table in production. It is stupid to rely on column order in queries anyway, so this is a completely unnecessary task. This is something that can cause big pain for literally no gain. Do not go down this road even if you find a workaround.