Resetuj seed tożsamości po usunięciu rekordów w SQL Server

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

Gdzie 0 jest identity wartością początkową

Należy zauważyć, że jeśli wszystkie danych są usuwane z tabeli za pomocą DELETE (tj. klauzuli no WHERE), to tak długo, jak zezwalają na to uprawnienia a) i b) nie ma FKs odwołujących się do tabeli (co wydaje się mieć miejsce w tym przypadku), użycie TRUNCATE TABLE byłoby preferowane, ponieważ czyni to bardziej wydajne DELETE i resetuje ziarno IDENTITY w tym samym czasie. Poniższe dane pochodzą ze strony MSDN dla TRUNCATE TABLE :

W porównaniu z Polecenie DELETE, tabela TRUNCATE ma następujące zalety:

• Zużywa się mniej miejsca w dzienniku transakcji.

  Instrukcja DELETE usuwa wiersze pojedynczo i zapisuje wpis w dzienniku transakcji dla każdego usuniętego wiersza. Tabela TRUNCATE usuwa dane poprzez dealokację stron danych używanych do przechowywania danych tabeli i zapisuje tylko dealokacje stron w dzienniku transakcji.

• Zazwyczaj stosuje się mniej zamków.

  Gdy Polecenie DELETE jest wykonywane za pomocą blokady wierszy, każdy wiersz w tabeli jest blokowany do usunięcia. TRUNCATE TABLE zawsze blokuje tabelę (włącznie z blokadą schematu (SCH-M)) i stronę, ale nie każdy wiersz.

• Bez wyjątku w tabeli pozostaje zero stron.

  Po wykonaniu polecenia DELETE, tabela może nadal zawierać puste strony. Na przykład puste strony w stercie nie mogą być rozdzielone bez co najmniej wyłącznej blokady tabeli (LCK_M_X). Jeśli usunąć operacja nie używa blokady tabeli, tabela (sterta) będzie zawierać wiele pustych stron. W przypadku Indeksów operacja usuwania może pozostawić puste strony, chociaż strony te zostaną szybko rozdzielone przez proces czyszczenia tła.

Jeśli tabela zawiera kolumnę identity, licznik dla tej kolumny jest resetowany do wartości początkowej zdefiniowanej dla kolumny. Jeśli nie zdefiniowano nasion, używana jest wartość domyślna 1. Aby zachować licznik tożsamości, użyj Usuń zamiast tego.

Tak więc:

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

Staje się tylko:

TRUNCATE TABLE [MyTable];

Proszę zapoznać się z dokumentacją TRUNCATE TABLE (link powyżej), aby uzyskać dodatkowe informacje na temat ograniczeń itp.

Próbowałem odpowiedzieć i to zresetowało tożsamość. Ale kiedy wstawiono nowy wiersz, otrzymano identity = 2. Zamiast tego zmieniłem składnię na:

DELETE FROM [TestTable]

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

Wtedy pierwszy wiersz otrzyma identity = 1.

Chociaż większość odpowiedzi sugeruje ponowne przesłanie do 0, ale wiele razy musimy po prostu ponownie przesłać do następnego Id dostępnego

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)

Spowoduje to sprawdzenie tabeli i zresetowanie do następnego ID.

Chociaż większość odpowiedzi sugeruje RESEED do 0, a niektórzy uważają to za wadę tabel TRUNCATED, Microsoft ma rozwiązanie, które wyklucza ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

Spowoduje to sprawdzenie tabeli i zresetowanie do następnego ID. Jest to dostępne od MS SQL 2005 do current.

Https://msdn.microsoft.com/en-us/library/ms176057.aspx

Aby jawnie podać wartość kolumny identity

1. pierwsza turn on identity insert - SET Identity_Insert tblPerson ON
2. w zapytaniu Wstaw podaj listę kolumn Insert into tblPerson(PersonId, Name) values(2, 'John')

Następnie masz wypełnione luki w kolumnie tożsamości i jeśli chcesz, aby SQL server obliczył wartość, Wyłącz Identity_Insert.

SET Identity_Insert tblPerson OFF

=============================

Jeśli usunąłeś wszystkie wiersze w tabeli i chcesz zresetować wartość kolumny identity.

Użyj DBCC / Align = "left" /

DBCC CHECKIDENT(tblPerson, RESEED, 0)

To polecenie zresetuje kolumnę tożsamości osoby.

@Jakub

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

Zadziałało dla mnie, po prostu musiałem najpierw wyczyścić wszystkie wpisy z tabeli, a następnie dodać powyższe w punkcie wyzwalania po usunięciu. Teraz za każdym razem, gdy usuwam wpis jest pobierany stamtąd.

To częste pytanie i odpowiedź jest zawsze taka sama: nie rób tego. Wartości tożsamości należy traktować jako arbitralne i jako takie nie ma "poprawnej" kolejności.

Wydanie 2 komendy może załatwić sprawę

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

Pierwszy zresetuje tożsamość do zera, a następny ustawi ją na następną dostępną wartość -- Jakub

Tabela Truncate jest preferowana, ponieważ czyści rekordy, resetuje licznik i odzyskuje przestrzeń dis.

Delete i CheckIdent powinny być używane tylko wtedy, gdy klucze obce uniemożliwiają obcinanie

Zresetuj kolumnę tożsamości z nowym id...

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

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

Uruchom ten skrypt, aby zresetować kolumnę tożsamości. Musisz wprowadzić dwie zmiany. Zastąp tableXYZ dowolną tabelą, którą chcesz zaktualizować. Ponadto nazwa kolumny tożsamości musi zostać usunięta z tabeli tymczasowej. To było natychmiastowe na tabeli z 35,000 wierszy i 3 kolumny. Oczywiście, wykonaj kopię zapasową tabeli i najpierw wypróbuj to w środowisku testowym.

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

Użyj tej procedury składowanej:

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'

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

Pierwszy wybór daje 0, Item 1.

Drugi produkuje 1, Item 1. Jeśli reset wykonasz zaraz po utworzeniu tabeli, następną wartością będzie 0. Szczerze mówiąc, nie dziwię się, że Microsoft nie może tego zrobić dobrze. Odkryłem to, ponieważ mam plik skryptu, który zapełnia tabele referencyjne, które czasami Uruchom po ponownym utworzeniu tabel, a czasami gdy tabele są już utworzone.

DBCC CHECKIDENT (<TableName>, reseed, 0)

Spowoduje ustawienie bieżącej wartości tożsamości na 0.

Po wstawieniu następnej wartości wartość tożsamości jest zwiększana do 1.

Dla kompletnego usuwania wierszy i resetowania liczby tożsamości, używam tego (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

Zawsze lepiej jest używać TRUNCATE , gdy jest to możliwe, zamiast usuwać wszystkie rekordy, ponieważ nie używa również przestrzeni logów.

W przypadku, gdy potrzebujemy delete i musimy zresetować seed, zawsze pamiętaj, że jeśli tabela nigdy nie była wypełniona i użyłeś DBCC CHECKIDENT('tablenem',RESEED,0) wtedy pierwszy rekord otrzyma identity = 0 jak podano w msdn documentation

W Twoim przypadku tylko Odbuduj indeks i nie martw się o utratę szereg tożsamości, gdyż jest to częsty scenariusz.

Najpierw: Specyfikacja Tożsamości Tylko: "Nie" > > Zapisz Bazę Danych Wykonaj Projekt

Następnie: Specyfikacja tożsamości tylko: "tak" > > Zapisz bazę danych wykonaj Projekt

Twój identyfikator bazy danych, PK zaczyna się od 1 > >