Sql-server – Add a new Column and define its position in a table

alter-tabledatabase-designsql-server-2008t-sql

I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?

Best Answer

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.

Related Solutions

Postgresql – How to specify the position for a new column in PostgreSQL

ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one. In order to create a new column in another position you need to recreate the table and copy the data from the old/current table in this new table.

New table or new column

I'd go with option 3, which will require less overall work:

Make a lookup table that contains a list of purged records and dates, like in your first option
Rename your current table to something like dbo.Tablename_master
Create a VIEW named dbo.Tablename which consists of:

SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)

Now you don't need to modify any existing code, since all references to your original table go through the view instead. This will be complicated if you have any existing constraints but they should be easy to work around.

Best Answer

Related Solutions

Postgresql – How to specify the position for a new column in PostgreSQL

New table or new column

Related Question