Zurücksetzen des Identitäts-Saatguts nach dem Löschen von Datensätzen in SQL Server

Question

Ich habe Datensätze in eine Tabelle einer SQL Server-Datenbank eingefügt. Die Tabelle hatte einen Primärschlüssel definiert und der automatische inkrementelle Identitäts-Seed ist auf "Ja" gesetzt. Dies geschieht primär, weil in SQL Azure jede Tabelle einen Primärschlüssel und eine Identität haben muss.

Aber da ich einige Datensätze aus der Tabelle löschen muss, wird der Identitäts-Seed für diese Tabellen gestört und die Indexspalte (die automatisch mit einem Inkrement von 1 generiert wird) wird gestört.

Wie kann ich die Identitätsspalte zurücksetzen, nachdem ich die Datensätze gelöscht habe, damit die Spalte eine Sequenz in aufsteigender numerischer Reihenfolge hat?

Die Identitätsspalte wird in der Datenbank nirgendwo als Fremdschlüssel verwendet.

Answer 1

Das Verwaltungskommando DBCC CHECKIDENT wird verwendet, um den Identitätszähler zurückzusetzen. Die Befehlssyntax lautet:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Beispiel:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

Es wurde in früheren Versionen der Azure SQL-Datenbank nicht unterstützt, wird aber jetzt unterstützt.

---

Dank Solomon Rutzky sind die docs für den Befehl jetzt korrigiert.

Answer 2

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Wo 0 ist der Identity Startwert

Answer 3

Auch wenn die meisten Antworten vorschlagen, auf 0 zurückzusetzen, müssen wir oft nur auf die nächste verfügbare ID zurücksetzen

declare @max int
select @max=max([Id]) from [TestTable]
if @max IS NULL   -- Überprüfen, wenn max als null zurückgegeben wird
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

Dies wird die Tabelle überprüfen und auf die nächste ID zurücksetzen.

Answer 4

Zu beachten ist, dass WENN alle Daten über das DELETE (d.h. ohne WHERE-Klausel) aus der Tabelle entfernt werden, dann, solange a) die Berechtigungen dies zulassen und b) keine FKs auf die Tabelle verweisen (was hier der Fall zu sein scheint), TRUNCATE TABLE bevorzugt wird, da es effizienter DELETE durchführt und gleichzeitig den IDENTITY-Seed zurücksetzt. Die folgenden Details stammen von der MSDN-Seite für TRUNCATE TABLE:

Verglichen mit dem DELETE-Statement hat TRUNCATE TABLE die folgenden Vorteile:

• Weniger Transaktionsprotokollplatz wird verwendet.

  Das DELETE-Statement entfernt Zeilen einzeln und protokolliert einen Eintrag im Transaktionsprotokoll für jede gelöschte Zeile. TRUNCATE TABLE entfernt die Daten, indem die Datenseiten, die zur Speicherung der Tabellendaten verwendet werden, deallokiert werden, und protokolliert nur die Seiten-Deallokationen im Transaktionsprotokoll.

• In der Regel werden weniger Sperren verwendet.

  Wenn das DELETE-Statement unter Verwendung einer Zeilensperre ausgeführt wird, wird jede Zeile in der Tabelle für die Löschung gesperrt. TRUNCATE TABLE sperrt immer die gesamte Tabelle (einschließlich einer Schemasperre (SCH-M)) und Seite, aber nicht jede Zeile.

• Ohne Ausnahme werden null Seiten in der Tabelle zurückgelassen.

  Nach Ausführung eines DELETE-Statements kann die Tabelle weiterhin leere Seiten enthalten. Zum Beispiel können leere Seiten in einem Heap nicht ohne mindestens eine exklusive (LCK_M_X) Tabellensperre deallokiert werden. Wenn die Löschoperation keine Tabellensperre verwendet, wird die Tabelle (der Heap) viele leere Seiten enthalten. Bei Indizes kann die Löschoperation leere Seiten zurücklassen, obwohl diese Seiten schnell von einem Hintergrundbereinigungsprozess deallokiert werden.

Wenn die Tabelle eine Identitätsspalte enthält, wird der Zähler für diese Spalte auf den für die Spalte definierten Seed-Wert zurückgesetzt. Wenn kein Seed definiert wurde, wird der Standardwert 1 verwendet. Um den Identitätszähler beizubehalten, verwenden Sie stattdessen DELETE.

Also wird das Folgende:

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

Einfach zu:

TRUNCATE TABLE [MyTable];

Bitte beachten Sie die Dokumentation zu TRUNCATE TABLE (oben verlinkt) für zusätzliche Informationen zu Einschränkungen usw.

Answer 5

Ich habe @anil shahs-Antwort ausprobiert und es hat die Identität zurückgesetzt. Aber als eine neue Zeile eingefügt wurde, wurde die Identität = 2. Also habe ich stattdessen die Syntax geändert:

DELETE FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

Dann wird die erste Zeile die Identität = 1 erhalten.