How to make auto increment of an existing primary key in Azure?

Question

How to make auto increment of an existing primary key in Azure?

Answer 1

Your table has to be rebuild from scratch, it's not possible to assign an auto_increment to an existing table. Try this script, wich will build a table for you with auto_increment, transfer all the data from your old table to the new one, drops the old table and renames the new one to the old one.

Just make sure that your data is compatable with the auto_increment property!

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_schema_version
    (
    version int NULL,
    datetime datetime NULL,
    comments nvarchar(150) NULL,
    id int NOT NULL IDENTITY (1, 1)
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_schema_version SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_schema_version ON
GO
IF EXISTS(SELECT * FROM dbo.schema_version)
     EXEC('INSERT INTO dbo.Tmp_schema_version (version, datetime, comments, id)
        SELECT version, datetime, comments, id FROM dbo.schema_version WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_schema_version OFF
GO
DROP TABLE dbo.schema_version
GO
EXECUTE sp_rename N'dbo.Tmp_schema_version', N'schema_version', 'OBJECT' 
GO
ALTER TABLE dbo.schema_version ADD CONSTRAINT
    PK_schema_version PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT