Setzen Sie den Identitätsstartwert nach dem Löschen von Datensätzen in SQL Server zurück

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 DELETEist 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.

https://msdn.Microsoft.com/de-de/library/ms176057.aspx

@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.