Add column2 before column1

Question

Hi,

i have table structure with 3 colums (column1, column2, column3) and i want to put another column with sql statement like:

alter table tbl_name add column4

but i need to put it between column1 and column2

may i do something like in MYSQL:

alter table tbl_name add column4 after column1

Answer 1

I don't think SQL Server allows you to do anything like that. If you want to put a column in the middle, you'll need to create a new table with the desired layout, migrate the data, delete the old table, and rename the new table.

Answer 2

@bdukes is correct. This is essentially what SSMS does when you add a column in any place other than the last position of the table. You could, however, achieve something similar using views. That is, simply add your column to the end of the table, then create a view which has the columns in a different order. Use the view instead of the actual table. I only offer this as one alternative that can be useful in certain situations. I'm not necessarily recommending it for your situation. Generally, I use the designer in SSMS and haven't had any problems with it updating tables when inserting a column. Backups, of course, are your friend!

Answer 3

A more fundamental question is why you want to do this? Other than the default display of columns in the SQL Enterprise manager, the order of columns is irrelevant. You can order the columns output by a SQL query any way you want, no matter how the columns are 'natively' ordered inside the database.

In fact, from an academic perspective, one of the cardinal properties of a RDBMS 'Relation' (the academic name for a table), is that the 'attributes' (columns) of a relation are unordered.

This is not to say that wanting the 'default' order to be a certain way is not reason enough... I often drop and recreate tables for exactly that reason... But just understand that the order doesn't mater for anything else.

Answer 4

in sql server 2005 you can save a change script of table changes, so for instance i created a test table, with col1, col2 and col3. Then added col4 between col1 and col2. I saved the change scripts and this is what it generated.

Have a look

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_zzzzzzzz
    (
    col1 nchar(10) NULL,
    col4 nchar(10) NULL,
    col2 nchar(10) NULL,
    col3 nchar(10) NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.zzzzzzzz)
     EXEC('INSERT INTO dbo.Tmp_zzzzzzzz (col1, col2, col3)
     SELECT col1, col2, col3 FROM dbo.zzzzzzzz WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.zzzzzzzz
GO
EXECUTE sp_rename N'dbo.Tmp_zzzzzzzz', N'zzzzzzzz', 'OBJECT' 
GO
COMMIT

seems like there is no way to simply add the column. Also, be aware of the transaction, this will drop the original table, so it is good to use a transaction