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

Vous ne pouvez pas supprimer un IDENTITY une fois qu'elle a été fixée.

Pour supprimer la colonne entière :

ALTER TABLE yourTable
DROP COLUMN yourCOlumn;

Informations sur ALTER TABLE ici

Si vous devez conserver les données, mais supprimer l'option IDENTITY colonne, vous devrez :

• Créer une nouvelle colonne
• Transférer les données de l'existant IDENTITY vers la nouvelle colonne
• Abandonner l'existant IDENTITY colonne.

Answer 2

Magic avec fonctionnalité de partitionnement (mais comme aucune partition n'est utilisée, vous n'avez pas besoin de l'édition Enterprise) :

1. Supprimer toutes les clés étrangères qui pointent vers cette table
2. script la table à créer ; renommez tout, par exemple 'MaTable2', 'MonIndex2', etc. Supprimez la spécification IDENTITY.
3. Vous devriez maintenant avoir deux tables "identiques", l'une pleine, l'autre vide, sans IDENTITÉ.
4. Exécuter ALTER TABLE [Original] SWITCH TO [Original2]
5. Maintenant, votre table originale sera vide et la nouvelle table contiendra les données. Vous avez échangé les métadonnées des deux tables (instant).
6. Abandonnez la table originale (maintenant vide), exec sys.sp_rename pour renommer les différents objets du schéma aux noms d'origine, puis vous pouvez recréer vos clés étrangères.

Par exemple, étant donné :

CREATE TABLE Original
(
  Id INT IDENTITY PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value ON Original (Value);

INSERT INTO Original
SELECT 'abcd'
UNION ALL 
SELECT 'defg';

Vous pouvez procéder comme suit :

--create new table with no IDENTITY
CREATE TABLE Original2
(
  Id INT PRIMARY KEY
, Value NVARCHAR(300)
);
CREATE NONCLUSTERED INDEX IX_Original_Value2 ON Original2 (Value);

--data before switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

ALTER TABLE Original SWITCH TO Original2;

--data after switch
SELECT 'Original', *
FROM Original
UNION ALL
SELECT 'Original2', *
FROM Original2;

--clean up
DROP TABLE Original;
EXEC sys.sp_rename 'Original2.IX_Original_Value2', 'IX_Original_Value', 'INDEX';
EXEC sys.sp_rename 'Original2', 'Original', 'OBJECT';

UPDATE Original
SET Id = Id + 1;

SELECT *
FROM Original;

Answer 3

Cela devient compliqué avec les contraintes de clés étrangères et primaires. Voici donc quelques scripts pour vous aider dans votre démarche :

Tout d'abord, créez une colonne dupliquée avec un nom temporaire :

alter table yourTable add tempId int NOT NULL default -1;
update yourTable set tempId = id;

Ensuite, obtenez le nom de votre contrainte de clé primaire :

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'yourTable';

Essayez maintenant de supprimer la contrainte de clé primaire pour votre colonne :

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;

Si vous avez des clés étrangères, cela va échouer, donc si c'est le cas, laissez tomber les contraintes de clés étrangères. GARDEZ LA TRACE DES TABLES POUR LESQUELLES VOUS EXÉCUTEZ CETTE OPÉRATION AFIN DE POUVOIR RAJOUTER LES CONTRAINTES PLUS TARD !!!

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'otherTable';
alter table otherTable drop constraint fk_otherTable_yourTable;
commit;
..

Une fois que toutes vos contraintes de clé étrangère ont été supprimées, vous pourrez supprimer la contrainte PK, abandonner cette colonne, renommer votre colonne temporaire et ajouter la contrainte PK à cette colonne :

ALTER TABLE yourTable DROP CONSTRAINT PK_yourTable_id;
alter table yourTable drop column id;
EXEC sp_rename 'yourTable.tempId', 'id', 'COLUMN';
ALTER TABLE yourTable ADD CONSTRAINT PK_yourTable_id PRIMARY KEY (id) 
commit;

Enfin, ajoutez à nouveau les contraintes du FK :

alter table otherTable add constraint fk_otherTable_yourTable foreign key (yourTable_id) references yourTable(id);
..

El Fin !

Answer 4

Je viens d'avoir le même problème. 4 déclarations dans SSMS au lieu d'utiliser l'interface graphique et c'était très rapide.

• Créer une nouvelle colonne

  alter table users add newusernum int;

• Copier les valeurs sur

  update users set newusernum=usernum;

• Abandonnez l'ancienne colonne

  alter table users drop column usernum;

• Renommez la nouvelle colonne avec le nom de l'ancienne colonne.

  EXEC sp_RENAME 'users.newusernum' , 'usernum', 'COLUMN';

Answer 5

Le script suivant supprime le champ Identité pour une colonne nommée 'Id'.

J'espère que cela vous aidera.

BEGIN TRAN
BEGIN TRY
    EXEC sp_rename '[SomeTable].[Id]', 'OldId';

    ALTER TABLE [SomeTable] ADD Id int NULL

    EXEC ('UPDATE [SomeTable] SET Id = OldId')

    ALTER TABLE [SomeTable] NOCHECK CONSTRAINT ALL

    ALTER TABLE [SomeTable] DROP CONSTRAINT [PK_constraintName];
    ALTER TABLE [SomeTable] DROP COLUMN OldId
    ALTER TABLE [SomeTable] ALTER COLUMN [Id] INTEGER NOT NULL
    ALTER TABLE [SomeTable] ADD CONSTRAINT PK_JobInfo PRIMARY KEY (Id)

    ALTER TABLE [SomeTable] CHECK CONSTRAINT ALL

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN   
    SELECT ERROR_MESSAGE ()
END CATCH