SQL Server에서 레코드를 삭제 한 후 ID 시드 재설정

SQL Server에서 레코드를 삭제 한 후 ID 시드 재설정

---

SQL Server 데이터베이스 테이블에 레코드를 삽입했습니다. 테이블에 기본 키가 정의되어 있고 자동 증분 ID 시드가 "예"로 설정되어 있습니다. 이는 주로 SQL Azure에서 각 테이블에 기본 키와 ID가 정의되어 있어야하기 때문에 수행됩니다.

그러나 테이블에서 일부 레코드를 삭제해야하기 때문에 해당 테이블의 ID 시드가 방해를 받고 인덱스 열 (1 씩 자동 생성됨)이 방해를받습니다.

열이 오름차순으로 순서를 갖도록 레코드를 삭제 한 후 ID 열을 재설정하려면 어떻게해야합니까?

ID 열은 데이터베이스의 어느 곳에서도 외래 키로 사용되지 않습니다.

---

DBCC CHECKIDENT관리 명령은 신원 카운터를 재설정하는 데 사용됩니다. 명령 구문은 다음과 같습니다.

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

예:

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

이전 버전의 Azure SQL Database에서는 지원되지 않았지만 지금은 지원됩니다.

---

유의하시기 바랍니다 new_reseed_value인수는 SQL Server 버전을 통해 변화되는 문서에 따라 :

테이블에 행이있는 경우 다음 행이 new_reseed_value 값으로 삽입됩니다 . SQL Server 2008 R2 및 이전 버전에서 삽입 된 다음 행은 new_reseed_value + 현재 증분 값을 사용합니다.

그러나 관찰 된 동작은 적어도 SQL Server 2012가 여전히 new_reseed_value + 현재 증분 값 논리를 사용하고 있음을 나타 내기 때문에이 정보가 오해의 소지가 있음을 발견했습니다 (실제로는 아주 잘못된 것입니다) . 마이크로 소프트 는 같은 페이지에서 발견 된 것과 모순된다 .Example C

C. 현재 ID 값을 새 값으로 강제

다음 예에서는 AddressType 테이블의 AddressTypeID 열에있는 현재 ID 값을 10으로 강제 설정합니다. 테이블에 기존 행이 있으므로 삽입 된 다음 행은 값으로 11을 사용합니다. 즉, 다음에 대해 정의 된 새 현재 증분 값입니다. 열 값에 1을 더한 값입니다.

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

그래도이 모든 것이 최신 SQL Server 버전에서 다른 동작에 대한 옵션을 남깁니다. Microsoft가 자체 문서에서 내용을 정리할 때까지 확실한 유일한 방법은 사용 전에 실제 테스트를 수행하는 것입니다.

---

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

0은 identity시작 값입니다.

---

이 경우 주목해야 하는 모든 데이터를 통해 테이블로부터 삭제되고 DELETE(즉 아니오 WHERE절) 다음만큼 a) 권한이 허용과 같이 b)로 표시되는 테이블 (참조 더 FKS 없다 여기의 경우), 사용하는 TRUNCATE TABLE것이 더 효율적 DELETE 이고IDENTITY 동시에 시드를 재설정 하므로 선호됩니다 . 다음 세부 정보는 TRUNCATE TABLE에 대한 MSDN 페이지에서 가져온 것입니다 .

DELETE 문과 비교할 때 TRUNCATE TABLE에는 다음과 같은 장점이 있습니다.

• 더 적은 트랜잭션 로그 공간이 사용됩니다.

  DELETE 문은 한 번에 하나씩 행을 제거하고 삭제 된 각 행에 대한 항목을 트랜잭션 로그에 기록합니다. TRUNCATE TABLE은 테이블 데이터를 저장하는 데 사용되는 데이터 페이지를 할당 해제하여 데이터를 제거하고 트랜잭션 로그에 페이지 할당 해제 만 기록합니다.

• 일반적으로 더 적은 수의 잠금이 사용됩니다.

  행 잠금을 사용하여 DELETE 문을 실행하면 테이블의 각 행이 삭제를 위해 잠 깁니다. TRUNCATE TABLE은 항상 테이블 (스키마 (SCH-M) 잠금 포함) 및 페이지를 잠그지 만 각 행은 잠급니다.

• 예외없이 테이블에 페이지가 없습니다.

  DELETE 문이 실행 된 후에도 테이블은 여전히 ​​빈 페이지를 포함 할 수 있습니다. 예를 들어, 힙의 빈 페이지는 최소한 배타적 (LCK_M_X) 테이블 잠금없이 할당을 취소 할 수 없습니다. 삭제 작업이 테이블 잠금을 사용하지 않는 경우 테이블 (힙)에 빈 페이지가 많이 포함됩니다. 인덱스의 경우 삭제 작업으로 인해 빈 페이지가 남을 수 있지만 이러한 페이지는 백그라운드 정리 프로세스에 의해 빠르게 할당 해제됩니다.

테이블에 ID 열이 포함 된 경우 해당 열의 카운터는 열에 대해 정의 된 시드 값으로 재설정됩니다. 시드가 정의되지 않은 경우 기본값 1이 사용됩니다. ID 카운터를 유지하려면 대신 DELETE를 사용하십시오.

따라서 다음과 같습니다.

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

다음과 같이됩니다.

TRUNCATE TABLE [MyTable];

TRUNCATE TABLE제한 등에 대한 추가 정보 는 문서 (위에 링크 됨)를 참조하십시오 .

---

대부분의 답변은 RESEED를 0으로 제안하지만 여러 번 사용 가능한 다음 ID로 다시 시드해야합니다.

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)

이것은 테이블을 확인하고 다음 ID로 재설정됩니다.

---

나는 @anil shahs대답을 시도 했고 그것은 신원을 재설정했습니다. 그러나 새 행이 삽입되면 identity = 2. 그래서 대신 구문을 다음과 같이 변경했습니다.

DELETE FROM [TestTable]

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

그런 다음 첫 번째 행은 ID = 1을 얻습니다.

---

대부분의 답변은 제안하고 있지만 RESEED에 0, 일부는의 결함으로 이것을 볼 때 TRUNCATED테이블, 마이크로 소프트는 그 해결책을 가지고 제외합니다ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

이것은 테이블을 확인하고 다음으로 재설정됩니다 ID. 이것은 MS SQL 2005부터 현재까지 사용 가능합니다.

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

---

2 명령을 내리면 트릭을 할 수 있습니다.

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

첫 번째는 ID를 0으로 재설정하고 다음은 사용 가능한 다음 값인 jacob로 설정합니다.

---

제이콥

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

나를 위해 일했지만 먼저 테이블에서 모든 항목을 지우고 삭제 후 트리거 지점에 위의 항목을 추가해야했습니다. 이제 항목을 삭제할 때마다 거기에서 가져옵니다.

---

이것은 일반적인 질문이며 대답은 항상 동일합니다.하지 마십시오. ID 값은 임의의 값으로 취급되어야하므로 "올바른"순서가 없습니다.

---

새 ID로 ID 열 재설정 ...

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

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

---

Truncate 테이블은 레코드를 지우고 카운터를 재설정하며 디스크 공간을 회수하기 때문에 선호됩니다.

Delete그리고 CheckIdent경우에만 외부 키가 절단하지 못하도록를 사용해야합니다.

---

Run this script to reset the identity column. You will need to make two changes. Replace tableXYZ with whatever table you need to update. Also, the name of the identity column needs dropped from the temp table. This was instantaneous on a table with 35,000 rows & 3 columns. Obviously, backup the table and first try this in a test environment.

---

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

---

DBCC CHECKIDENT (<TableName>, reseed, 0)

This will set the current identity value to 0.

On inserting the next value, the identity value get incremented to 1.

---

Use this stored procedure:

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'

Just revisiting my answer. I came across a weird behaviour in sql server 2008 r2 that you should be aware of.

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

The first select produces 0, Item 1.

The second one produces 1, Item 1. If you execute the reset right after the table is created the next value is 0. Honestly, I am not surprised Microsoft cannot get this stuff right. I discovered it because I have a script file that populates reference tables that I sometimes run after I re-create tables and sometimes when the tables are already created.

---

For a complete DELETE rows and reset the IDENTITY count, I use this (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

---

I use the following script to do this. There's only one scenario in which it will produce an "error", which is if you have deleted all rows from the table, and IDENT_CURRENT is currently set to 1, i.e. there was only one row in the table to begin with.

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)
;

---

Reseeding to 0 is not very practical unless you are cleaning up the table as a whole.

other wise the answer given by Anthony Raymond is perfect. Get the max of identity column first, then seed it with max.

---

Its always better to use TRUNCATE when possible instead of deleting all records as it doesn't use log space also.

In case we need delete and need to reset the seed, always remember that if table was never populated and you used DBCC CHECKIDENT('tablenem',RESEED,0) then first record will get identity = 0 as stated on msdn documentation

In your case only rebuild the index and don't worry about losing the series of identity as this is a common scenario.

---

First : Identity Specification Just : "No" >> Save Database Execute Project

After then : Identity Specification Just : "YES" >> Save Database Execute Project

Your Database ID, PK Start from 1 >>

참고URL : https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server