How to add new column to a MSSQL table between two existing columns

It’s possible but not as easy as ALTER TABLE [table] ADD COLUMN [column] [type] AFTER [anothercolumn]; like supported in MySql.

We need to drop all contraints, create a temporary table with the new structure, copy all data to the temporary table, drop the old table and rename the temporary table.

Below is a script that adds the column newcolumn between place and name in a table named mytable.

ALTER TABLE [mytable] DROP CONSTRAINT [PK_mytable]
ALTER TABLE [mytable] DROP CONSTRAINT [DF_mytable_place]
ALTER TABLE [mytable] DROP CONSTRAINT [DF_mytable_name]
 
CREATE TABLE tmp_mytable (
	[item_id] [int] IDENTITY(1,1) NOT NULL,
	[place] [int] NOT NULL,
	[newcolumn] [nvarchar](255) NOT NULL,
	[name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
	[item_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
ALTER TABLE [tmp_mytable] ADD  CONSTRAINT [DF_mytable_place]  DEFAULT ((1)) FOR [place]
ALTER TABLE [tmp_mytable] ADD  CONSTRAINT [DF_mytable_name]  DEFAULT ('') FOR [name]
ALTER TABLE [tmp_mytable] ADD  CONSTRAINT [DF_mytable_newcolumn]  DEFAULT ('newvalue') FOR [newcolumn]
 
SET IDENTITY_INSERT dbo.tmp_mytable ON
IF EXISTS(SELECT * FROM [mytable])
	 EXEC('INSERT INTO [tmp_mytable] (item_id, place, name)
		SELECT item_id, place, name FROM [mytable] WITH (HOLDLOCK TABLOCKX)')
SET IDENTITY_INSERT dbo.tmp_mytable OFF
 
DROP TABLE mytable;
EXECUTE sp_rename N'tmp_mytable', N'mytable', 'OBJECT';

If you don’t care about the placement you can add a column to the end of the table using this command:

ALTER TABLE mytable ADD newcolumn [nvarchar](255) NOT NULL CONSTRAINT DF_mytable_newcolumn DEFAULT 'newvalue'