Eine übliche Praxis in Data Warehouse Tabellen ist es, den Schlüssel der Tabelle mit einem Autoinkrement zu befüllen. Diese Funktionalität stellt der MS SQL Server schon von Haus aus bereit mit der sog. Identity-Spalte, welche fast immer auch gleichzeitig den Primary Clustered Index enthält. Soweit so gut, aber was muss ich tun, wenn ich meine Identitätsspalte auf einen bestimmten initialen Wert zurücksetzen will?

Sollte dieser Fall eintreten, dann muss zunächst zwischen zwei bestimmten Fällen unterschieden werden:

 

  • Ich möchte die Identitätsspalte auf den Standardwert oder 1 zurücksetzen.

 

 

  • Ich möchte die Identitätspalte auf einen benutzerdefinierten Wert setzen.

 

Fall 1 ist hier zumeist die Regel und kann entsprechend leicht abgedeckt werden. Wir nehmen an dieser Stelle an, dass die Daten der betreffenden Tabelle bereits gesichert wurden. Dann ist das Statement unserer Wahl der TRUNCATE TABLE Befehl

TRUNCATE TABLE dbo.TableWithIdentity

Dieser Befehl bewirkt nicht nur, dass alle Daten einer Tabelle gelöscht werden, sondern veranlasst quasi auch ein „Zurücksetzen auf Werkseinstellungen“. Konkret werden alle Identitätsspalten der Tabelle ebenfalls auf den Standardwert (oder 1 falls nicht angegeben) zurückgesetzt. Somit kann nachfolgend die Tabelle neu befüllt werden, wobei der Zähler wieder von vorn beginnt, hochzuzählen. Aufgabe gelöst.

Schwieriger wird es bei Fall 2. Dieser kann z.B. eintreten, wenn ich eine Archiv-Tabelle zwar leeren, jedoch nicht den Zähler zurücksetzen möchte. Hier sollen schließlich alle neu eingefügten Datensätze mit der ursprünglichen Nummer fortlaufen. Die naheliegendste Lösung ist einfach die Zeilen mit DELETE anstatt mit TRUNCATE zu löschen. Da Archivtabellen meist jedoch sehr groß sind, lohnt es sich nicht das Delete Statement zu nutzen, da es einerseits langsam ist (Row by agonizing Row) und außerdem den Transaction Log zu sehr belastet. Was tun also?

In diesem Fall muss ich auf eine Kombination aus TRUNCATE TABLE und der DBCC Funktion CHECKIDENT(‘table_name’, RESEED, new_reseed_value) zurückgreifen. Diese ermöglicht es zwar, den Zähler nach dem TRUNCATE auf eine beliebige Position zu setzen, beinhaltet jedoch mehrere Stolpersteine. Auf einen der Wichtigsten wollen wir näher eingehen. Es ist nämlich ein Unterschied, ob ich das folgende Statement auf eine mit DELETE oder auf eine mit TRUNCATE geleerte Tabelle abfeuere, wie in folgendem Listing zu sehen.

-- Create the test tables
CREATE TABLE dbo.TableWithIdentity1 (ID int not null Identity (1,1),Value int null);
CREATE TABLE dbo.TableWithIdentity2 (ID int not null Identity (1,1),Value int null);

-- insert some test values
INSERT INTO dbo.TableWithIdentity1 (Value)
VALUES (2), (1), (3), (2);
INSERT INTO dbo.TableWithIdentity2 (Value)
VALUES (2), (1), (3), (2);

-- save the current identity values with the function IDENT_CURRENT
Declare @IdentCurrent1 int = (select IDENT_CURRENT('dbo.TableWithIdentity1'));
Declare @IdentCurrent2 int = (select IDENT_CURRENT('dbo.TableWithIdentity2'));

-- delete the table values
DELETE FROM dbo.TableWithIdentity1;
TRUNCATE TABLE dbo.TableWithIdentity2;

-- apply checkident
DBCC CHECKIDENT ('dbo.TableWithIdentity1', reseed, @IdentCurrent1);
DBCC CHECKIDENT ('dbo.TableWithIdentity2', reseed, @IdentCurrent2);

-- insert new values
INSERT INTO dbo.TableWithIdentity1 (Value)
SELECT 10;
INSERT INTO dbo.TableWithIdentity2 (Value)
SELECT 10;

-- show results
Select * from dbo.TableWithIdentity1; -- resolves ID 5
Select * from dbo.TableWithIdentity2; -- resolves ID 4

Dies hängt mit einer weitgehend unbekannten Eigenschaft der Funktion zusammen. Auf neu erstellte oder mit TRUNCATE geleerte Tabellen angewendet, wird der in new_reseed_value angegebene Wert für die nächste eingefügte Zeile verwendet. Für nicht mehr jungfräuliche Tabellen wird dagegen new_reseed_value + aktuelles Inkrement (lässt sich durch IDENT_INCR ( ‘TableName’ anzeigen)) angewendet. Dies führt bei Nichtbeachtung mitunter zu frustrierenden Ergebnissen und Eindeutigkeitsverletzungen im Key. Es ist daher zu raten, vor der Nutzung von CHECKIDENT wenn möglich IMMER ein TRUNCATE TABLE vorzustellen, da man nie weiß, welcher Prozess vorher mit welchem Befehl die Tabelle bearbeitet hat.