확인 - SQL Server에서 레코드를 삭제 한 후 ID 시드 다시 설정




mssql identity 확인 (12)

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

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

열을 숫자 순서로 오름차순으로 정렬하도록 레코드를 삭제 한 후 ID 열을 어떻게 다시 설정할 수 있습니까?

식별 C 럼은 데이터베이스의 외부 키로 사용되지 않습니다.


@jacob

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

나를 위해 일한, 난 그냥 테이블에서 모든 항목을 먼저 지우고 삭제 후 트리거 지점에서 위의 추가했다. 지금은 내가 항목을 삭제할 때마다 거기에서 가져옵니다.


나는 @anil shahs 대답을 시도하고 신원을 다시 설정합니다. 그러나 새로운 행이 삽입되면 identity = 2 됩니다. 대신 구문을 다음과 같이 변경했습니다.

DELETE FROM [TestTable]

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

그런 다음 첫 번째 행에 ID = 1이 생깁니다.


대부분의 대답은 RESEED0 으로 제안하지만 일부는 이것을 TRUNCATED 테이블의 결함으로 간주하지만 Microsoft는 ID 를 제외하는 솔루션을 제공합니다

DBCC CHECKIDENT ('[TestTable]', RESEED)

그러면 테이블이 점검되고 다음 ID 재설정됩니다. 이것은 현재까지 MS SQL 2005부터 사용 가능합니다.

MSDN


발급 2 명령 트릭을 할 수있는

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

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


이 스크립트를 실행하여 ID 열을 재설정하십시오. 두 가지를 변경해야합니다. tableXYZ를 업데이트해야하는 테이블로 바꾸십시오. 또한 식별 컬럼의 이름은 임시 테이블에서 제거해야합니다. 이것은 35,000 개의 행과 3 개의 열이있는 테이블에서 즉시 발생했습니다. 분명히, 테이블을 백업하고 먼저 테스트 환경에서 이것을 시도하십시오.

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

이 저장 프로 시저를 사용하십시오.

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'

내 대답을 다시 방문하십시오. 나는 당신이 알고 있어야 SQL 서버 2008 R2에서 이상한 행동을 가로 질러 왔어.

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

첫 번째 select는 0, Item 1 생성합니다.

두 번째 것은 1, Item 1 생성합니다. 테이블을 만든 직후에 리셋을 실행하면 다음 값은 0이됩니다. 솔직히 Microsoft가이 일을 제대로 수행 할 수 없다는 사실에 놀라지 않습니다. 내가 테이블을 다시 생성 한 다음 때때로 테이블이 이미 생성 된 후에 실행되는 참조 테이블을 채우는 스크립트 파일이 있기 때문에 발견했습니다.


첫 번째 : 신원 명세서 Just : "No">> 데이터베이스 저장 프로젝트 실행

그 이후 : 신원 명세서 Just : "YES">> 데이터베이스 저장 프로젝트 실행

데이터베이스 ID, PK 1에서 시작 >>


항상 로그 공간을 사용하지 않으므로 모든 레코드를 삭제하는 대신 가능한 경우 TRUNCATE 를 사용하는 것이 좋습니다.

우리가 삭제를 필요로하고 시드를 재설정 할 필요가있는 경우, 테이블에 데이터가 채워지지 않았고 DBCC CHECKIDENT('tablenem',RESEED,0) 했다면 MSDN 명시된 것처럼 첫 번째 레코드에 ID = 0이 기록됩니다

귀하의 경우에만 인덱스를 다시 작성 하고 이것이 일반적인 시나리오이므로 일련의 신원을 잃어 버릴 염려는하지 마십시오.


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

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

예:

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

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

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 버전에서 다른 동작을위한 옵션을 제공합니다. 마이크로 소프트가 자체 문서에서 정리할 때까지는 사용하기 전에 실제 테스트를 수행하는 것이 유일한 방법이라고 생각합니다.


Truncate 테이블은 레코드를 지우고 카운터를 재설정하고 dis 공간을 다시 차지하기 때문에 선호됩니다.

DeleteCheckIdent 는 외부 키 CheckIdent 를 수없는 경우에만 사용해야합니다.


DBCC CHECKIDENT (<TableName>, reseed, 0)

그러면 현재 ID 값이 0으로 설정됩니다.

다음 값을 삽입하면 ID 값이 1로 증가합니다.


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

여기서 0은 identity 시작 값





azure-sql-database