Ajout d'une identité à une colonne existante

Question

Je dois changer la clé primaire d'une table en une colonne d'identité, et il y a déjà un certain nombre de lignes dans la table.

J'ai un script pour nettoyer les ID afin de s'assurer qu'ils sont séquentiels à partir de 1, cela fonctionne bien sur ma base de données de test.

Quelle est la commande SQL pour modifier la colonne afin qu'elle ait une propriété d'identité ?

Answer 1

Vous ne pouvez pas modifier les colonnes existantes pour l'identité.

Vous avez deux options,

1. Créez une nouvelle table avec une identité et supprimez la table existante.

2. Créez une nouvelle colonne avec une identité et déposez la colonne existante.

Approche 1. ( Nouvelle table ) Ici, vous pouvez conserver les valeurs de données existantes sur la colonne d'identité nouvellement créée.

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approche 2 ( Nouvelle colonne ) Vous ne pouvez pas conserver les valeurs de données existantes sur la colonne d'identité nouvellement créée. La colonne d'identité contiendra la séquence de numéros.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Pour plus de détails, consultez l'article suivant du forum Microsoft SQL Server :

Comment modifier une colonne en identity(1,1) ?

Answer 2

En SQL 2005 et supérieur, il existe une astuce pour résoudre ce problème sans modifier les pages de données de la table. C'est important pour les grandes tables où toucher chaque page de données peut prendre des minutes ou des heures. L'astuce fonctionne également si la colonne d'identité est une clé primaire, si elle fait partie d'un index clusterisé ou non clusterisé, ou d'autres problèmes qui peuvent faire échouer la solution plus simple "ajouter/supprimer/renommer la colonne".

Voici l'astuce : vous pouvez utiliser la fonction MODIFIER LA TABLE...CHANGER pour modifier le schéma d'une table sans modifier les données, ce qui signifie que vous pouvez remplacer une table avec une IDENTITÉ par un schéma de table identique, mais sans colonne d'IDENTITÉ. La même astuce fonctionne pour ajouter l'IDENTITÉ à une colonne existante.

Normalement, MODIFIER LA TABLE...CHANGER est utilisé pour remplacer efficacement une partition pleine dans une table partitionnée par une nouvelle partition vide. Mais elle peut également être utilisée dans des tables non partitionnées.

J'ai utilisé cette astuce pour convertir, en moins de 5 secondes, une colonne d'une table de 2,5 milliards de lignes d'IDENTITY en non-IDENTITY (afin d'exécuter une requête de plusieurs heures dont le plan de requête fonctionnait mieux avec des colonnes non-IDENTITY), puis j'ai rétabli le paramètre IDENTITY, toujours en moins de 5 secondes.

Voici un exemple de code qui illustre son fonctionnement.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- see same records
 SELECT * FROM Test; 

C'est évidemment plus compliqué que les solutions proposées dans les autres réponses, mais si votre table est grande, cela peut vous sauver la vie. Il y a quelques mises en garde :

• vous devrez abandonner les clés étrangères avant de faire l'échange et les restaurer après.
• de même pour les fonctions WITH SCHEMABINDING, les vues, etc.
• Les index de la nouvelle table doivent correspondre exactement (mêmes colonnes, même ordre, etc.).
• L'ancienne et la nouvelle table doivent être dans le même groupe de fichiers.
• ne fonctionne que sur SQL Server 2005 ou plus récent
• Je pensais auparavant que cette astuce ne fonctionnait que sur les éditions Enterprise ou Developer de SQL Server (parce que les partitions ne sont prises en charge que dans les versions Enterprise et Developer), mais Mason G. Zhwiti, dans son commentaire ci-dessous, indique qu'elle fonctionne également dans SQL Standard Edition. Je suppose que cela signifie que la restriction à Enterprise ou Developer ne s'applique pas à ALTER TABLE...SWITCH.

Il y a une bonne article sur TechNet détaillant les exigences ci-dessus.

Answer 3

Vous ne pouvez pas modifier une colonne pour qu'elle soit une colonne IDENTITY. Vous devez créer une nouvelle colonne qui est définie comme une IDENTITÉ dès le départ, puis supprimer l'ancienne colonne et renommer la nouvelle colonne avec l'ancien nom.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

Answer 4

Il y a une solution cool décrite ici : SQL SERVER - Ajouter ou supprimer la propriété Identity sur une colonne

En bref, éditez manuellement votre table dans SQL Manager, changez l'identité, NE SAUVEGARDEZ PAS les changements, montrez juste le script qui sera créé pour les changements, copiez-le et utilisez-le plus tard.

C'est un énorme gain de temps, car il (le script) contient toutes les clés étrangères, les indices, etc. liés à la table que vous modifiez. Écrire cela manuellement... Dieu nous en préserve.

Answer 5

Par conception, il n'y a pas de moyen simple d'activer ou de désactiver la fonction d'identité pour une colonne existante. La seule façon propre de le faire est de créer une nouvelle colonne et d'en faire une colonne d'identité ou de créer une nouvelle table et de migrer vos données.

Si nous utilisons SQL Server Management Studio pour nous débarrasser de la valeur d'identité sur la colonne "id", une nouvelle table temporaire est créée, les données sont déplacées vers la table temporaire, l'ancienne table est supprimée et la nouvelle table est renommée.

Utilisez Management Studio pour effectuer la modification, puis faites un clic droit dans le concepteur et sélectionnez "Generate Change script".

Vous verrez que c'est ce que fait le serveur SQL en arrière-plan.