Add Column to an existing table and mention position of the column

Hi all,
Can anybody please help me in this case.

I have a table test(NAME,CITY)
Now i want to add a new column "ID" as primary key to table test and the position of the table should be before column "NAME".

If possible, Please suggest

Thanks
Bob

You can not do that using T-SQL. The way "Enterprise Manager" or "SSMS" does it, is creating a new table with layout you want, copying data, dropping constraint that references the old table, dropping the old table, renaming the new table and recreating all constraints and indexes.

In the relational world, the position of the column is not important.

AMB

|||The only way you can do that is to create a new table, then transfer the data from the old table to the new one, then drop the old table, and finally, rename the new table to the old table's name.|||

Are their any good examples for this action, i thinking it would be easier to add new columns to the aspnet_Profile Table. To expand the user's profile data into single columns instead of one blob of data that is potentially useless. When using a SqlDataProvider for the fact the data in the row comes out in one long string of information.

Unless theirs away to break that information up into useful sections for a FormView or GridView..