Add an identity column to a table with data

Method: Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.

-- Create a test table and insert data
CREATE TABLE DataTable(id int, name varchar(5) )
INSERT INTO DataTable(id, name)
VALUES(1, 'A'),
(3, 'B'),
(5, 'C'),
(6, 'D')
GO

--Create a temp table with an identity column
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_DataTable
(
id int NOT NULL IDENTITY (1, 1),
name VARCHAR(20) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable ON
GO
IF EXISTS (SELECT 1 FROM dbo.DataTable)
INSERT INTO dbo.Tmp_DataTable (id, name)
SELECT id, name FROM dbo.DataTable WITH (HOLDLOCK TABLOCKX)
GO
SET IDENTITY_INSERT dbo.Tmp_DataTable OFF
GO
DROP TABLE dbo.DataTable
GO
EXECUTE sp_rename N'dbo.Tmp_DataTable', N'DataTable', 'OBJECT'
GO
COMMIT

Notice that the data of an identity column does not have to be contiguous, thought it has to be in ascending order. Caution must be exercised here to drop and recreate all table constraints and relations.