azure - 如何在Azure中自动递增现有主键？

Question

如何在 Azure 中自动递增现有主键？

Answer 1

您的表必须从头开始重建，不可能将 auto_increment 分配给现有表。尝试这个脚本，它将为您构建一个带有 auto_increment 的表，将所有数据从旧表传输到新表，删除旧表并将新表重命名为旧表。

只需确保您的数据与 auto_increment 属性兼容即可!

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