SQL Server - Supprimer l'identité d'une colonne dans une table

Question

Nous avons une table de 5 Go (près de 500 millions de lignes) et nous voulons supprimer la propriété d'identité sur l'une des colonnes, mais lorsque nous essayons de le faire par SSMS - il s'arrête.

Cela peut-il être fait par T-SQL ?

Nous utilisons SQL Server 2005 Enterprise Edition

Answer 1

Le code ci-dessous fonctionne très bien, quand on ne connaît pas le nom de la colonne d'identité .

J'ai besoin de copier des données dans une nouvelle table temporaire comme Invoice_DELETED . et la prochaine fois, nous utiliserons :

insert into Invoice_DELETED select * from Invoice where ...

SELECT t1.*
INTO Invoice_DELETED
FROM Invoice t1
LEFT JOIN Invoice ON 1 = 0
--WHERE t1.InvoiceID = @InvoiceID

Pour plus d'explications, voir : https://dba.stackexchange.com/a/138345/101038

Answer 2

ALTER TABLE tablename add newcolumn int
update tablename set newcolumn=existingcolumnname
ALTER TABLE tablename DROP COLUMN existingcolumnname;
EXEC sp_RENAME 'tablename.oldcolumn' , 'newcolumnname', 'COLUMN'

Cependant, le code ci-dessus ne fonctionne que s'il n'y a pas de relation clé primaire-clé étrangère.

Answer 3

Dans SQL Server, vous pouvez activer et désactiver l'insertion d'identité comme suit :

SET IDENTITY_INSERT nom_table ON

-- exécutez vos requêtes ici

SET IDENTITY_INSERT nom_table OFF

Answer 4

Juste pour quelqu'un qui a eu le même problème que moi. Si vous voulez juste faire un insert une seule fois, vous pouvez faire quelque chose comme ça.

Supposons que vous ayez un tableau avec deux colonnes

ID Identity (1,1) | Name Varchar

et je veux insérer une ligne avec l'ID = 4. Vous le remplacez donc par 3 pour que le suivant soit 4.

DBCC CHECKIDENT([YourTable], RESEED, 3)

Faire l'insert

INSERT  INTO [YourTable]
        ( Name )
VALUES  ( 'Client' )

Et ramenez votre graine à l'ID le plus élevé, supposons que c'est 15.

DBCC CHECKIDENT([YourTable], RESEED, 15)

C'est fait !

Answer 5

J'avais la même exigence, et vous pourriez essayer cette façon, que je vous recommande personnellement, s'il vous plaît concevoir manuellement votre table et générer le script, et ce que j'ai fait ci-dessous était de renommer l'ancienne table et aussi sa contrainte pour la sauvegarde.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
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
ALTER TABLE dbo.SI_Provider_Profile
    DROP CONSTRAINT DF_SI_Provider_Profile_SIdtDateTimeStamp
GO
ALTER TABLE dbo.SI_Provider_Profile
    DROP CONSTRAINT DF_SI_Provider_Profile_SIbHotelPreLoaded
GO
CREATE TABLE dbo.Tmp_SI_Provider_Profile
    (
    SI_lProvider_Profile_ID int NOT NULL,
    SI_lSerko_Integrator_Token_ID int NOT NULL,
    SI_sSerko_Integrator_Provider varchar(50) NOT NULL,
    SI_sSerko_Integrator_Profile varchar(50) NOT NULL,
    SI_dtDate_Time_Stamp datetime NOT NULL,
    SI_lProvider_ID int NULL,
    SI_sDisplay_Name varchar(10) NULL,
    SI_lPurchased_From int NULL,
    SI_sProvider_UniqueID varchar(255) NULL,
    SI_bHotel_Pre_Loaded bit NOT NULL,
    SI_sSiteName varchar(255) NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
    DF_SI_Provider_Profile_SIdtDateTimeStamp DEFAULT (getdate()) FOR SI_dtDate_Time_Stamp
GO
ALTER TABLE dbo.Tmp_SI_Provider_Profile ADD CONSTRAINT
    DF_SI_Provider_Profile_SIbHotelPreLoaded DEFAULT ((0)) FOR SI_bHotel_Pre_Loaded
GO
IF EXISTS(SELECT * FROM dbo.SI_Provider_Profile)
        EXEC('INSERT INTO dbo.Tmp_SI_Provider_Profile (SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName)
        SELECT SI_lProvider_Profile_ID, SI_lSerko_Integrator_Token_ID, SI_sSerko_Integrator_Provider, SI_sSerko_Integrator_Profile, SI_dtDate_Time_Stamp, SI_lProvider_ID, SI_sDisplay_Name, SI_lPurchased_From, SI_sProvider_UniqueID, SI_bHotel_Pre_Loaded, SI_sSiteName FROM dbo.SI_Provider_Profile WITH (HOLDLOCK TABLOCKX)')
GO

-- Rename the primary key constraint or unique key In SQL Server constraints such as primary keys or foreign keys are objects in their own right, even though they are dependent upon the "containing" table.
EXEC sp_rename 'dbo.SI_Provider_Profile.PK_SI_Provider_Profile', 'PK_SI_Provider_Profile_Old';
GO
-- backup old table in case of 
EXECUTE sp_rename N'dbo.SI_Provider_Profile', N'SI_Provider_Profile_Old', 'OBJECT'
GO

EXECUTE sp_rename N'dbo.Tmp_SI_Provider_Profile', N'SI_Provider_Profile', 'OBJECT'
GO

ALTER TABLE dbo.SI_Provider_Profile ADD CONSTRAINT
    PK_SI_Provider_Profile PRIMARY KEY NONCLUSTERED 
    (
    SI_lProvider_Profile_ID
    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT TRANSACTION