Hinzufügen einer Identität zu einer vorhandenen Spalte

Question

Ich muss den Primärschlüssel einer Tabelle in eine Identitätsspalte ändern, und es gibt bereits eine Reihe von Zeilen in der Tabelle.

Ich habe ein Skript, um die IDs zu bereinigen, um sicherzustellen, dass sie bei 1 beginnend sequentiell sind, funktioniert gut auf meine Testdatenbank.

Wie lautet der SQL-Befehl, um die Spalte so zu ändern, dass sie eine Identitätseigenschaft hat?

Answer 1

Sie können die vorhandenen Spalten für die Identität nicht ändern.

Sie haben 2 Möglichkeiten,

1. Erstellen Sie eine neue Tabelle mit Identität und löschen Sie die vorhandene Tabelle

2. Erstellen Sie eine neue Spalte mit Identität und lassen Sie die vorhandene Spalte fallen.

Ansatz 1. ( Neue Tabelle ) Hier können Sie die vorhandenen Datenwerte in der neu erstellten Identitätsspalte beibehalten. Beachten Sie, dass alle Daten verloren gehen, wenn die Bedingung "if not exists" nicht erfüllt ist, stellen Sie also sicher, dass Sie die Bedingung auch auf die Drop-Spalte anwenden!

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'

Ansatz 2 ( Neue Spalte ) Sie können die vorhandenen Datenwerte in der neu erstellten Identitätsspalte nicht beibehalten. Die Identitätsspalte wird die Zahlenfolge enthalten.

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'

Weitere Informationen finden Sie im folgenden Beitrag im Microsoft SQL Server-Forum:

Wie ändert man eine Spalte in Identität(1,1)

Answer 2

In SQL 2005 und höher gibt es einen Trick, um dieses Problem zu lösen, ohne die Datenseiten der Tabelle zu ändern. Dies ist wichtig für große Tabellen, bei denen das Anfassen jeder Datenseite Minuten oder Stunden dauern kann. Der Trick funktioniert auch, wenn die Identitätsspalte ein Primärschlüssel ist, Teil eines geclusterten oder nicht geclusterten Indexes ist oder andere Probleme auftauchen, die die einfachere Lösung "Spalte hinzufügen/entfernen/umbenennen" zum Scheitern bringen können.

Hier ist der Trick: Sie können die SQL Server-Funktion ALTER TABLE...SWITCH Anweisung können Sie das Schema einer Tabelle ändern, ohne die Daten zu ändern, d.h. Sie können eine Tabelle mit IDENTITY durch ein identisches Tabellenschema, aber ohne IDENTITY-Spalte, ersetzen. Der gleiche Trick funktioniert, um IDENTITY zu einer vorhandenen Spalte hinzuzufügen.

Normalerweise, ALTER TABLE...SWITCH wird verwendet, um eine volle Partition in einer partitionierten Tabelle effizient durch eine neue, leere Partition zu ersetzen. Es kann aber auch in nicht partitionierten Tabellen verwendet werden.

Ich habe diesen Trick verwendet, um in weniger als 5 Sekunden eine Spalte einer Tabelle mit 2,5 Milliarden Zeilen von IDENTITY in eine Non-IDENTITY-Spalte zu konvertieren (um eine mehrstündige Abfrage auszuführen, deren Abfrageplan für Non-IDENTITY-Spalten besser funktionierte), und dann die IDENTITY-Einstellung wiederhergestellt, wiederum in weniger als 5 Sekunden.

Hier ist ein Code-Beispiel, wie es funktioniert.

 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';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

Dies ist natürlich aufwändiger als die Lösungen in den anderen Antworten, aber wenn Ihr Tisch groß ist, kann dies ein echter Lebensretter sein. Es gibt einige Vorbehalte:

• Soweit ich weiß, ist die Identität das Einzige, was Sie mit dieser Methode an den Spalten Ihrer Tabelle ändern können. Das Hinzufügen/Entfernen von Spalten, das Ändern der Nullbarkeit usw. ist nicht erlaubt.
• Sie müssen die ausländischen Schlüssel vor dem Wechsel ablegen und sie danach wiederherstellen.
• Dasselbe gilt für WITH SCHEMABINDING-Funktionen, Ansichten usw.
• die Indizes der neuen Tabelle müssen genau übereinstimmen (gleiche Spalten, gleiche Reihenfolge usw.)
• Alte und neue Tabellen müssen sich in der gleichen Dateigruppe befinden.
• Funktioniert nur auf SQL Server 2005 oder höher
• Ich habe bisher geglaubt, dass dieser Trick nur auf den Enterprise- oder Developer-Editionen von SQL Server funktioniert (weil Partitionen nur in den Enterprise- und Developer-Versionen unterstützt werden), aber Mason G. Zhwiti sagt in seinem Kommentar unten, dass er auch in der SQL Standard Edition funktioniert. Ich nehme an, dass dies bedeutet, dass die Beschränkung auf Enterprise oder Developer nicht für ALTER TABLE...SWITCH gilt.

Es gibt eine gute Artikel im TechNet in dem die oben genannten Anforderungen im Einzelnen aufgeführt sind.

UPDATE - Eric Wu hatte unten einen Kommentar, der wichtige Informationen zu dieser Lösung enthält. Ich kopiere ihn hier, um sicherzustellen, dass er mehr Aufmerksamkeit erhält:

Hier gibt es noch einen weiteren Vorbehalt, der erwähnenswert ist. Obwohl die neue Tabelle gerne Daten aus der alten Tabelle erhält und alle neuen Zeilen neuen Zeilen nach einem identischen Muster eingefügt werden, werden sie bei 1 beginnen und möglicherweise abbrechen, wenn die besagte Spalte ein Primärschlüssel ist. Betrachten Sie die Ausführung DBCC CHECKIDENT('<newTableName>') unmittelbar nach Wechsel. Siehe msdn.microsoft.com/de-us/library/ms176057.aspx für mehr Informationen.

Wenn die Tabelle aktiv mit neuen Zeilen erweitert wird (was bedeutet, dass Sie nicht viel oder gar keine Ausfallzeit zwischen dem Hinzufügen von IDENTITY und dem Hinzufügen neuer Zeilen haben), dann sollten Sie anstelle von DBCC CHECKIDENT müssen Sie den Identitäts-Seed-Wert im neuen Tabellenschema manuell so einstellen, dass er größer ist als die größte vorhandene ID in der Tabelle, z. B. IDENTITY (2435457, 1) . Möglicherweise können Sie sowohl die ALTER TABLE...SWITCH und die DBCC CHECKIDENT in einer Transaktion (oder nicht - ich habe das nicht getestet), aber es scheint, dass es einfacher und sicherer ist, den Seed-Wert manuell zu setzen.

Wenn der Tabelle keine neuen Zeilen hinzugefügt werden (oder nur gelegentlich, z. B. bei einem täglichen ETL-Prozess), tritt diese Race Condition natürlich nicht auf. DBCC CHECKIDENT ist in Ordnung.

Answer 3

Sie können eine Spalte nicht so ändern, dass sie eine IDENTITY-Spalte ist. Sie müssen eine neue Spalte erstellen, die von Anfang an als IDENTITY-Spalte definiert ist, dann die alte Spalte löschen und die neue Spalte in den alten Namen umbenennen.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

Marc

Answer 4

Erwägen Sie die Verwendung SEQUENCE anstelle von IDENTITY .

In Sql Server 2014 (ich weiß nicht, wie es mit niedrigeren Versionen aussieht) können Sie dies einfach mit Sequence tun.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

Von hier aus: Sequenz als Standardwert für eine Spalte

Answer 5

Hier wird eine coole Lösung beschrieben: SQL SERVER - Identitätseigenschaft einer Spalte hinzufügen oder entfernen

Kurz gesagt, bearbeiten Sie Ihre Tabelle manuell im SQL Manager, wechseln Sie die Identität, speichern Sie die Änderungen NICHT, zeigen Sie nur das Skript, das für die Änderungen erstellt wird, kopieren Sie es und verwenden Sie es später.

Es ist eine enorme Zeitersparnis, da es (das Skript) alle Fremdschlüssel, Indizes usw. für die Tabelle enthält, die Sie ändern. Dies manuell zu schreiben... Gott bewahre!