SQL Server, Comment définir l'incrémentation automatique après la création d'une table sans perte de données ?

Question

J'ai une table table1 dans SQL Server 2008 et il contient des enregistrements.

Je veux que la clé primaire table1_Sno pour être une colonne à incrémentation automatique. Cela peut-il être fait sans transfert de données ou clonage de la table ?

Je sais que je peux utiliser ALTER TABLE pour ajouter une colonne à incrémentation automatique, mais puis-je simplement ajouter l'option AUTO_INCREMENT à une colonne existante qui est la clé primaire ?

Answer 1

Changer le IDENTITY est en fait un changement qui ne concerne que les métadonnées. Mais pour mettre à jour les métadonnées directement, il faut démarrer l'instance en mode utilisateur unique et modifier certaines colonnes dans le fichier sys.syscolpars et est non documenté/non pris en charge et n'est pas quelque chose que je recommanderais ou sur lequel je donnerais des détails supplémentaires.

Pour les personnes qui rencontrent cette réponse sur SQL Server 2012+, la façon la plus simple d'obtenir ce résultat d'une colonne à incrémentation automatique serait de créer un fichier de type SEQUENCE et définir l'objet next value for seq comme colonne par défaut.

Alternativement, ou pour les versions précédentes (à partir de 2005), la solution de contournement postée sur ce point de connexion montre une manière complètement supportée de faire ceci sans aucun besoin de taille des opérations de données utilisant ALTER TABLE...SWITCH . Également sur le blog de MSDN ici . Bien que le code pour y parvenir ne soit pas très simple et qu'il y ait des restrictions - par exemple, la table qui est modifiée ne peut pas être la cible d'une contrainte de clé étrangère.

Exemple de code.

Installez la table d'essai sans identity colonne.

CREATE TABLE dbo.tblFoo 
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)

INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2

Modifiez-le pour avoir un identity colonne (plus ou moins instantanée).

BEGIN TRY;
    BEGIN TRANSACTION;

    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
      set the correct seed in the table definition instead*/
    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        bar INT IDENTITY(' + 
                     CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        filler CHAR(8000),
        filler2 CHAR(49)
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@TableScript)

    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

Testez le résultat.

INSERT INTO dbo.tblFoo (filler,filler2) 
OUTPUT inserted.*
VALUES ('foo','bar')

Donne

bar         filler    filler2
----------- --------- ---------
10001       foo       bar      

Nettoyer

DROP TABLE dbo.tblFoo

Answer 2

SQL Server : Comment définir l'auto-incrémentation sur une table contenant des lignes :

Cette stratégie copie physiquement les lignes deux fois, ce qui peut prendre beaucoup plus de temps si la table que vous copiez est très grande.

Vous pourriez sauvegarder vos données, supprimer et reconstruire la table avec l'auto-incrément et la clé primaire, puis charger à nouveau les données.

Je vais vous guider à l'aide d'un exemple :

Etape 1, créer la table foobar (sans clé primaire ou auto-incrément) :

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

Étape 2, insérer quelques lignes

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

Etape 3, copier les données de foobar dans une table temporaire :

select * into temp_foobar from foobar

Etape 4, abandon de la table foobar :

drop table foobar;

Étape 5, recréez votre table avec les propriétés de clé primaire et d'auto-incrément :

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

Etape 6, réinsérez vos données de la table temporaire dans foobar.

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

Étape 7, déposez votre table temporaire, et vérifiez si cela a fonctionné :

drop table temp_foobar;
select * from foobar;

Vous devriez obtenir ceci, et lorsque vous inspectez la table foobar, la colonne id est auto-incrémentée de 1 et id est une clé primaire :

1    one
2    two
3    three

Answer 3

Si, vous le pouvez. Allez à Outils > Designers > Table et Designers et décochez "Empêcher la sauvegarde des changements qui empêchent la récréation de la table" .

Answer 4

Si vous ne voulez pas ajouter une nouvelle colonne et que vous pouvez garantir que votre colonne int actuelle est unique, vous pouvez sélectionner toutes les données dans une table temporaire, supprimer la table et la recréer en spécifiant la colonne IDENTITY. Ensuite, en utilisant SET IDENTITY INSERT ON vous pouvez insérer toutes vos données de la table temporaire dans la nouvelle table.

Answer 5

Non, vous ne pouvez pas ajouter une option d'incrémentation automatique à une colonne existante avec des données, je pense que l'option que vous avez mentionnée est la meilleure.

Jetez un coup d'œil ici .