How to alter an existing column in a table to become a primary key/identity column?

USE [test]

GO

/****** Objekt: Table [dbo].[Customer] Skriptdatum: 12/18/2007 11:04:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Customer](

      [ID] [int] NOT NULL,

      [SomeText] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL

) ON [PRIMARY]

/* Überprüfen Sie das Skript ausführlich, bevor Sie es außerhalb des Datenbank-Designer-Kontexts ausführen, um potenzielle Datenverluste zu vermeiden.*/

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_Customer

      (

      ID int NOT NULL IDENTITY (1, 1),

      SomeText nvarchar(50) NOT NULL

      ) ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_Customer ON

GO

IF EXISTS(SELECT * FROM dbo.Customer)

      EXEC('INSERT INTO dbo.Tmp_Customer (ID, SomeText)

            SELECT ID, SomeText FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_Customer OFF

GO

DROP TABLE dbo.Customer

GO

EXECUTE sp_rename N'dbo.Tmp_Customer', N'Customer', 'OBJECT'

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

      PK_Customer PRIMARY KEY CLUSTERED

      (

      ID

      ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT