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'
