SQL Server -- Any way to add a column and make it first column in table?

Question

I'm altering an existing table to add an Identity column. That I can do, no problem.

But I'm wanting to be sure that people who look at it in the future will see that it has the identity column added, so I really want to make it column 1. I know this is totally inconsequential to the system's operation; it's strictly for human reading.

Does anyone know of a way to do this? I've looked at the TSQL syntax for Alter Table and for column_definition, and don't see anything; but I'm hoping someone knows of a way to make this happen.

FWIW, this is a one-time operation (but on many servers, so it needs to be automated), so I'm not worried whether any "trick" might go away in the future -- as long as it works now. We're using recent versions of SQL Server Express.

Thanks for any suggestions.

Answer 1

Solve this by following these steps:

-- First, add identity column
alter table
    mytable

add
    id int identity(1, 1) not null

-- Second, create new table from existing one with correct column order
select
    id,
    col1,
    col2

into
    newtable

from
    mytable

Now you've got newtable with reordered columns. If you need to you can drop your mytable and rename newtable to mytable:

drop table
    mytable

exec sp_rename
    'newtable', 'mytable'