Why can't I reorder my SQL Server columns?

Question

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.

Answer 1

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!

Answer 2

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