Reset Startwert nach dem löschen von Datensätzen in SQL Server

Den DBCC CHECKIDENT management-Befehl wird verwendet, um die reset-Identität Zähler. Der Befehl syntax ist:

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

Beispiel:

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

War es nicht unterstützt in früheren Versionen von Azure SQL-Datenbank, aber wird jetzt unterstützt.

Bitte beachten Sie, dass new_reseed_value argument ist vielfältig über SQL Server-Versionen gemäß der Dokumentation:

Wenn Zeilen vorhanden sind, werden in der Tabelle die nächste Zeile eingefügt wird, mit der new_reseed_value Wert. In der version SQL Server 2008 R2 und früher, die nächste eingefügte Zeile verwendet new_reseed_value + das aktuelle Inkrement-Wert.

Jedoch ich finde diese Informationen irreführend (einfach nur falsch), da beobachtete Verhalten zeigt an, dass mindestens der SQL Server 2012 ist immer noch verwendet new_reseed_value + das aktuelle Inkrement-Wert-Logik. Microsoft selbst im Widerspruch mit seiner eigenen Example C gefunden auf derselben Seite:

C. Zwingt die aktuelle identity-Wert auf einen neuen Wert

Dem folgenden Beispiel wird der aktuelle Identitätswert in der
AddressTypeID Spalte in der Tabelle AddressType auf einen Wert von 10.
Da die Tabelle vorhandene Zeilen hat, verwendet die nächste eingefügte Zeile verwenden 11
als Wert, das heißt, die neue aktuelle Schrittweite festgelegten Wert für die
Spalte Wert plus 1.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

Immer noch, dies alles hinterlässt eine option für das unterschiedliche Verhalten auf neuere SQL Server-Versionen. Ich denke, der einzige Weg, um sicher zu sein, bis Microsoft aufzuräumen, die Dinge in seine eigene Dokumentation, zu tun, die die eigentlichen tests vor der Verwendung.

InformationsquelleAutor der Antwort Petr Abdulin

Es sollte angemerkt werden, dass, WENN alle der Daten aus der Tabelle entfernt über die DELETE (d.h. keine WHERE - Klausel), dann solange a) die Berechtigungen dies zulassen, und b) es gibt keine FKs verweisen auf die Tabelle (das scheint hier der Fall sein), mit TRUNCATE TABLE bevorzugt würde, da er ein effizienter DELETE und setzt die IDENTITY Samen zur gleichen Zeit. Die folgenden Angaben sind entnommen aus dem MSDN-Seite für TRUNCATE TABLE:

Im Vergleich zu den DELETE-Anweisung TRUNCATE TABLE hat folgende Vorteile:

• Weniger Speicherplatz im Transaktionsprotokoll verwendet wird.

  Die DELETE-Anweisung entfernt Zeilen ein zu einer Zeit und nimmt einen Eintrag in das Transaktionslog für jede gelöschte Zeile. TRUNCATE TABLE löscht die Daten durch freigeben der Daten Seiten zum speichern von Daten in der Tabelle und zeichnet nur die aufgehobenen seitenzuordnungen in das Transaktionsprotokoll.

• Weniger sperren sind in der Regel verwendet.

  Wenn die DELETE-Anweisung ausgeführt wird, mithilfe einer Zeile sperren, die jede Zeile in der Tabelle zum löschen gesperrt. TRUNCATE TABLE sperrt immer die Tabelle (schema (SCH-M) - lock) und die Seite, nicht aber jede Zeile.

• Ohne Ausnahme, null Seiten sind Links in der Tabelle.

  Nach einer DELETE-Anweisung kann die Tabelle weiterhin leere Seiten enthalten. Zum Beispiel, leere Seiten in einem heap werden nicht freigegeben, wenn mindestens eine exklusive (LCK_M_X) Tabelle sperren. Wenn der Löschvorgang nicht mithilfe einer Tabelle sperren, die Tabelle (heap) enthält viele leere Seiten. Für Indizes können durch den Löschvorgang leere Seiten verbleiben, obwohl diese Seiten werden freigegeben, ist schnell von einem hintergrund-cleanup-Prozess.

Wenn die Tabelle eine Identitätsspalte enthält, wird der Zähler für die Spalte zurücksetzen, um die seed-Wert für die Spalte definiert ist. Wenn kein Ausgangswert definiert wurde, wird der Standardwert 1 verwendet. Behalten Sie die Identität der Zähler, verwenden Sie LÖSCHEN, statt.

Also Folgendes:

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

Wird nur:

TRUNCATE TABLE [MyTable];

Finden Sie die TRUNCATE TABLE Dokumentation (weiter oben verlinkt) weitere Informationen zu den Einschränkungen, etc.

InformationsquelleAutor der Antwort Solomon Rutzky

Versuchte ich @anil shahs Antwort und zurücksetzen der Identität. Aber wenn eine neue Zeile eingefügt wurde, bekam Sie die identity = 2. Anstatt also, ich habe die syntax:

DELETE FROM [TestTable]

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

Dann wird die erste Zeile, um die Identität = 1.

InformationsquelleAutor der Antwort Mikael Engver

@jacob

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

Arbeitete für mich, ich musste Sie nur deaktivieren Sie alle Einträge aus der Tabelle, dann die oben in einen Triggerpunkt nach löschen. Jetzt, wenn ich einen Eintrag löschen wird von dort entnommen.

InformationsquelleAutor der Antwort epic

Ausstellung 2-Befehl kann den trick tun

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

dem ersten reset die Identität zu null , und die nächste wird es auf den nächsten verfügbaren Wert
-- Jakob

InformationsquelleAutor der Antwort jacob

Reset-identity-Spalte mit neuen id...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

InformationsquelleAutor der Antwort Mukesh Pandey

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'

Nur der Rückgriff auf meine Antwort. Stieß ich auf ein seltsames Verhalten in sql server 2008 r2, die Sie sich bewusst sein 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

Den ersten wählen Sie produziert 0, Item 1.

Die zweite produziert 1, Item 1. Wenn Sie führen Sie das reset direkt nach dem erstellen der Tabelle der nächste Wert ist 0. Ehrlich gesagt bin ich nicht überrascht, dass Microsoft kann nicht dieses Zeug. Entdeckt habe ich es, weil ich eine Skript-Datei, die füllt Referenz-Tabellen, die ich manchmal laufen, nachdem ich wieder Tabellen erstellen und manchmal, wenn die Tabellen bereits angelegt sind.

InformationsquelleAutor der Antwort costa

Für eine komplette Zeilen LÖSCHEN und zurücksetzen der IDENTITÄT zählen, ich benutze dieses (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

InformationsquelleAutor der Antwort Fandango68

Erste : Identität-Spezifikation Nur : "No" >> Datenbank Speichern Ausführen-Projekt

Dann : Identität-Spezifikation Nur : "YES" >> Datenbank Speichern Ausführen-Projekt

Ihre Datenbank-ID, PK-Start von 1 >>

InformationsquelleAutor der Antwort Pratik Patel