Ich habe Datensätze in eine SQL Server-Datenbanktabelle eingefügt. In der Tabelle wurde ein Primärschlüssel definiert, und der Startwert für die automatische Inkrement-Identität wird auf "Ja" gesetzt. Dies geschieht hauptsächlich, weil in SQL Azure für jede Tabelle ein Primärschlüssel und eine Identität definiert werden müssen.
Da ich jedoch einige Datensätze aus der Tabelle löschen muss, wird der Identitätsstartwert 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, sodass die Spalte in aufsteigender numerischer Reihenfolge angeordnet ist?
Die Identitätsspalte wird an keiner Stelle in der Datenbank als Fremdschlüssel verwendet.
Der Befehl DBCC CHECKIDENT
management 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 jedoch jetzt unterstützt.
Bitte beachten Sie, dass das new_reseed_value
-Argument in den verschiedenen SQL Server-Versionen variiert wird je nach Dokumentation :
Wenn Zeilen in der Tabelle vorhanden sind, wird die nächste Zeile mit dem Wert new_reseed_value eingefügt. In der Version SQL Server 2008 R2 und früheren Versionen verwendet die nächste eingefügte Zeile new_reseed_value + den aktuellen Inkrementwert.
Ich finde diese Informationen jedoch irreführend (nur einfach falsch), da das beobachtete Verhalten darauf hinweist, dass mindestens SQL Server 2012 noch new_reseed_value + die aktuelle Logik des Inkrementwerts verwendet. Microsoft widerspricht sogar seinem eigenen Example C
auf derselben Seite:
C. Erzwingen des aktuellen Identitätswerts auf einen neuen Wert
Das folgende Beispiel erzwingt den aktuellen Identitätswert in der AddressTypeID-Spalte in der AddressType-Tabelle mit einem Wert von 10 . Da in der Tabelle bereits Zeilen vorhanden sind, wird für die nächste eingefügte Zeile 11 .__ verwendet. als Wert ist dies der neue aktuelle Inkrementwert, der für die .__ definiert ist. Spaltenwert plus 1.
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
Trotzdem bleibt eine Option für ein anderes Verhalten in neueren SQL Server-Versionen erhalten. Ich glaube, der einzige Weg, um sicher zu gehen, bis Microsoft die Dinge in seiner eigenen Dokumentation aufklärt, besteht darin, tatsächliche Tests vor der Verwendung durchzuführen.
DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO
Dabei ist 0 identity
Startwert
Es sollte beachtet werden, dass IF alle der Daten über die Variable DELETE
aus der Tabelle entfernt werden (dh keine WHERE
-Klausel). Solange a) die Berechtigungen dies zulassen, und b) es keine FKs gibt, die auf das verweisen Tabelle (was hier der Fall zu sein scheint), wäre die Verwendung von TRUNCATE TABLE
vorzuziehen, da dies eine effizientere DELETE
ist und den IDENTITY
-Samen gleichzeitig zurücksetzt. Die folgenden Details stammen von der MSDN-Seite für TRUNCATE TABLE :
Im Vergleich zur DELETE-Anweisung bietet TRUNCATE TABLE folgende Vorteile:
Es wird weniger Transaktionsprotokollspeicherplatz verwendet.
Die DELETE-Anweisung entfernt Zeilen einzeln und zeichnet für jede gelöschte Zeile einen Eintrag im Transaktionsprotokoll auf. TRUNCATE TABLE entfernt die Daten durch Aufheben der Zuordnung der zum Speichern der Tabellendaten verwendeten Datenseiten und zeichnet nur die Seitenaufhebungen im Transaktionsprotokoll auf.
In der Regel werden weniger Schlösser verwendet.
Wenn die DELETE-Anweisung mit einer Zeilensperre ausgeführt wird, wird jede Zeile in der Tabelle zum Löschen gesperrt. TRUNCATE TABLE sperrt immer die Tabelle (einschließlich einer Schemasperre (SCH-M)) und die Seite, jedoch nicht jede Zeile.
Ausnahmslos bleiben Nullseiten in der Tabelle.
Nachdem eine DELETE-Anweisung ausgeführt wurde, kann die Tabelle noch leere Seiten enthalten. Zum Beispiel können leere Seiten in einem Heap nicht freigegeben werden, ohne mindestens eine exklusive Tabellensperre (LCK_M_X). Wenn der Löschvorgang keine Tabellensperre verwendet, enthält die Tabelle (Heap) viele leere Seiten. Bei Indizes kann der Löschvorgang leere Seiten zurücklassen, obwohl diese Seiten durch einen Hintergrundbereinigungsprozess schnell freigegeben werden.
Wenn die Tabelle eine Identitätsspalte enthält, wird der Zähler für diese Spalte auf den für die Spalte definierten Ausgangswert zurückgesetzt. Wenn kein Startwert definiert wurde, wird der Standardwert 1 verwendet. Um den Identitätszähler beizubehalten, verwenden Sie stattdessen DELETE.
Also das Folgende:
DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);
Wird gerade:
TRUNCATE TABLE [MyTable];
Weitere Informationen zu Einschränkungen usw. finden Sie in der TRUNCATE TABLE
-Dokumentation (oben verlinkt).
Obwohl die meisten Antworten auf RESEED auf 0 hinweisen, müssen wir oft nur die nächste verfügbare ID erneut eingeben
declare @max int
select @max=max([Id])from [TestTable]
if @max IS NULL //check when max is returned as null
SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED,@max)
Dadurch wird die Tabelle überprüft und auf die nächste ID zurückgesetzt.
Ich habe versucht, @anil shahs
zu beantworten und die Identität zurückzusetzen. Wenn jedoch eine neue Zeile eingefügt wurde, erhielt sie den identity = 2
. Also habe ich stattdessen die Syntax geändert:
DELETE FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO
Dann erhält die erste Reihe die Identität = 1.
Obwohl die meisten Antworten RESEED
für 0
vorschlagen, und obwohl einige dies als Fehler für TRUNCATED
-Tabellen sehen, hat Microsoft eine Lösung, die ID
ausschließt.
DBCC CHECKIDENT ('[TestTable]', RESEED)
Dadurch wird die Tabelle überprüft und auf die nächste ID
zurückgesetzt. Dies ist seit MS SQL 2005 verfügbar.
@Jacob
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
Für mich gearbeitet, musste ich nur alle Einträge zuerst aus der Tabelle löschen und dann den obigen Punkt in einen Triggerpunkt nach dem Löschen einfügen. Wenn ich jetzt lösche, wird ein Eintrag von dort genommen.
Identitätsspalte mit neuer ID zurücksetzen ...
DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]
DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)
der Befehl 2 kann den Trick ausführen
DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)
das erste setzt die Identität auf Null zurück und das nächste setzt es auf den nächsten verfügbaren Wert -- Jacob
Dies ist eine häufig gestellte Frage und die Antwort ist immer die gleiche: Tu es nicht. Identitätswerte sollten als willkürlich behandelt werden und daher gibt es keine "richtige" Reihenfolge.
Die Truncate
-Tabelle wird bevorzugt, weil sie die Datensätze löscht, den Zähler zurücksetzt und den Speicherplatz zurückfordert.
Delete
und CheckIdent
sollten nur verwendet werden, wenn Fremdschlüssel das Abschneiden verhindern
Führen Sie dieses Skript aus, um die Identitätsspalte zurückzusetzen. Sie müssen zwei Änderungen vornehmen. Ersetzen Sie tableXYZ durch die Tabelle, die Sie aktualisieren möchten. Außerdem muss der Name der Identitätsspalte aus der temporären Tabelle gelöscht werden. Dies war für eine Tabelle mit 35.000 Zeilen und 3 Spalten augenblicklich. Sichern Sie die Tabelle und versuchen Sie es zuerst in einer Testumgebung.
select *
into #temp
From tableXYZ
set identity_insert tableXYZ ON
truncate table tableXYZ
alter table #temp drop column (nameOfIdentityColumn)
set identity_insert tableXYZ OFF
insert into tableXYZ
select * from #temp
Verwenden Sie diese gespeicherte Prozedur:
IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
BEGIN
EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pResetIdentityField]
@pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max INT;
DECLARE @fullTableName NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;
DECLARE @identityColumn NVARCHAR(1000);
SELECT @identityColumn = c.[name]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE c.is_identity = 1
AND t.name = @pTableName
AND s.[name] = @pSchemaName
IF @identityColumn IS NULL
BEGIN
RAISERROR(
'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
, 16
, 1);
RETURN;
END;
DECLARE @sqlString NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;
EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT
IF @max IS NULL
SET @max = 0
print(@max)
DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go
--exec pResetIdentityField 'dbo', 'Table'
Ich habe meine Antwort noch einmal besucht. Ich habe in SQL Server 2008 R2 ein seltsames Verhalten gefunden, das Sie kennen sollten.
drop table test01
create table test01 (Id int identity(1,1), descr nvarchar(10))
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01
delete from test01
execute pResetIdentityField 'dbo', 'test01'
insert into test01 (descr) values('Item 1')
select * from test01
Die erste Auswahl erzeugt 0, Item 1
.
Die zweite produziert 1, Item 1
. Wenn Sie das Rücksetzen direkt nach dem Erstellen der Tabelle ausführen, ist der nächste Wert 0. Ehrlich gesagt bin ich nicht überrascht, dass Microsoft das nicht richtig machen kann. Ich habe es entdeckt, weil ich eine Skriptdatei habe, die Referenztabellen auffüllt, die ich manchmal ausführe, nachdem ich Tabellen neu erstellt habe und manchmal, wenn die Tabellen bereits erstellt wurden.
Das Zurücksetzen auf 0 ist nicht sehr praktisch, es sei denn, Sie räumen den gesamten Tisch auf.
ansonsten ist die Antwort von Anthony Raymond perfekt. Holen Sie sich zuerst die Spalte max of identity und setzen Sie sie dann mit max.
Für vollständige DELETE-Zeilen und das Zurücksetzen der IDENTITY-Zählung verwende ich dies (SQL Server 2008 R2)
USE mydb
-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################
DECLARE
db_cursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_CATALOG = 'mydb'
DECLARE @tblname VARCHAR(50)
SET @tblname = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname
WHILE @@FETCH_STATUS = 0
BEGIN
IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
BEGIN
EXEC('DELETE FROM ' + @tblname)
DBCC CHECKIDENT (@tblname, RESEED, 0)
END
FETCH NEXT FROM db_cursor INTO @tblname
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
Ich verwende dazu das folgende Skript. Es gibt nur ein Szenario, in dem ein "Fehler" erzeugt wird. Dies ist der Fall, wenn Sie alle Zeilen aus der Tabelle gelöscht haben und IDENT_CURRENT
derzeit auf 1 festgelegt ist, d.
DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;
IF @maxID IS NULL
IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
ELSE
DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
;
ELSE
DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;
DBCC CHECKIDENT (<TableName>, reseed, 0)
Dadurch wird der aktuelle Identitätswert auf 0 gesetzt.
Beim Einfügen des nächsten Werts wird der Identitätswert auf 1 erhöht.