SQL Server 데이터베이스에서 ID 증가가 점프하고 있습니다.

SQL Server 데이터베이스에서 ID 증가가 점프하고 있습니다.

내 테이블 중 하나에서FeeSQL Server 2012의 "ReceiptNo" 열에서 다음 두 가지 사항에 따라 데이터베이스 ID 증분이 갑자기 1이 아닌 100으로 급증하기 시작했습니다.

1. 1205446이면 1206306, 1206321이면 1207306, 1207314이면 1208306으로 껑충 뛰어오릅니다.다음 그림과 같이 점프가 발생할 때마다 마지막 세 자리가 일정하게 유지됩니다.

2. 이 문제는 컴퓨터를 다시 시작할 때 발생합니다.

SQL Server 2012 이후 성능이 향상되었기 때문에 이러한 동작이 발생하고 있습니다.

으로 1,000 캐시 를 할당할 때 1 크기를 합니다.IDENTITY의 값int을 표시하고 시작하면 되지 않는 값이 "수 있습니다(는 "" ""에 대해 10입니다").bigint/numeric).

이는 설명서에 언급되어 있습니다.

SQL Server는 성능상의 이유로 ID 값을 캐시할 수 있으며 할당된 값 중 일부는 데이터베이스 오류 또는 서버 다시 시작 중에 손실될 수 있습니다.이로 인해 삽입 시 ID 값에 공백이 발생할 수 있습니다.공백이 허용되지 않는 경우 응용 프로그램은 자체 메커니즘을 사용하여 키 값을 생성해야 합니다. NOCACHE옵션을 사용하면 커밋되지 않은 트랜잭션의 간격을 제한할 수 있습니다.

데이터를 이후 했을 때 SQL Server가 처럼 보입니다.1206306 - 1207305 범위인 12/24~25를 했습니다.1207306 - 120830528일의 에서 볼 수 .

비정상적인 빈도로 서비스를 다시 시작하지 않는 한 "잃어버린" 값은 데이터 유형에서 허용하는 값 범위에 큰 영향을 미치지 않으므로 이에 대해 걱정하지 않는 것이 최선의 정책입니다.

이 문제가 어떤 이유로 인해 발생하는 경우 몇 가지 해결 방법은 다음과 같습니다.

1. ID 열 대신 를 사용하고 더 작은 캐시 크기를 정의할 수 있습니다. 예를 들어NEXT VALUE FOR열을 기본값으로 설정합니다.
2. 또는 추적 플래그 272를 적용하여 다음을 만듭니다.IDENTITY할당은 2008 R2 버전까지 기록되었습니다.이것은 모든 데이터베이스에 전역적으로 적용됩니다.
3. 버전의 에는 는최버경실행우의전을 합니다.ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF특정 데이터베이스에 대한 ID 캐싱을 비활성화합니다.

이러한 해결 방법 중 어떤 것도 차이를 보장하지 않는다는 것을 알아야 합니다.이것은 결코 에 의해 보장되지 않았습니다.IDENTITY테이블에 삽입물을 직렬화해야만 가능하기 때문입니다.이 없는 에는 둘 중 하나가 .IDENTITY또는SEQUENCE

이 문제는 SQL Server를 다시 시작한 후에 발생합니다.

솔루션은 다음과 같습니다.

• SQL Server 구성 관리자를 실행합니다.

• SQL Server 서비스를 선택합니다.

  

• SQL Server를 마우스 오른쪽 버튼으로 클릭하고 속성을 선택합니다.

• 시작 매개변수 아래의 시작 창에서 다음을 입력합니다.-T272추가를 클릭한 다음 적용 버튼을 누르고 다시 시작합니다.

  

ALTER 데이터베이스 범위 구성을 사용할 수 있습니다.

IDITY_CACHE = {ON | OFF}

데이터베이스 수준에서 ID 캐시를 사용하거나 사용하지 않도록 설정합니다.기본값은 ON입니다.ID 캐싱은 ID 열이 있는 테이블에서 INSERT 성능을 향상시키는 데 사용됩니다.서버가 예기치 않게 다시 시작되거나 보조 서버로 페일오버되는 경우 ID 열 값의 차이를 방지하려면 IDENTITY_CACH 옵션을 사용하지 않도록 설정합니다.이 옵션은 서버 수준에서만 설정할 수 있는 것이 아니라 데이터베이스 수준에서 설정할 수 있다는 점을 제외하면 기존 SQL Server 추적 플래그 272와 유사합니다.

(...)

G. IDENTITY_CACH 설정

이 예에서는 ID 캐시를 사용하지 않도록 설정합니다.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

저는 제 대답이 파티에 늦을 수도 있다는 것을 알고 있습니다.하지만 SQL Server 2012에서 시작 저장 프로시저를 추가하여 다른 방법으로 해결했습니다.

마스터 DB에 다음 저장 프로시저를 작성합니다.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

그런 다음 다음 구문을 사용하여 시작에 추가합니다.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

테이블이 적다면 이것은 좋은 생각입니다.하지만 많은 테이블에 대해 해야 한다면, 이 방법은 여전히 효과가 있지만 좋은 생각은 아닙니다.

이것은 규모에 관계없이 여전히 많은 개발자와 응용 프로그램에서 매우 일반적인 문제입니다.

안타깝게도 위의 제안이 모든 시나리오를 수정하지는 않습니다.공유 호스팅, 호스트에서 -t272 시작 매개 변수를 설정할 수 없습니다.

또한 이러한 ID 열을 기본 키로 사용하는 기존 테이블이 있는 경우에는 해당 열을 삭제하고 BS 시퀀스 해결 방법을 사용하기 위해 새 열을 재생성하는 것이 매우 어렵습니다.시퀀스 해결 방법은 SQL 2012+에서 테이블을 처음부터 새로 설계하는 경우에만 유용합니다.

중요한 것은 SQL Server 2008R2를 사용하는 경우에는 그대로 유지하는 것입니다.진짜로, 계속해요.Microsoft가 SQL Server 2016에도 여전히 존재하는 거대 버그를 도입했다는 사실을 인정하기 전까지는 이 버그를 소유하고 IT를 수정할 때까지 업그레이드해서는 안 됩니다.

Microsoft는 다시 시작할 때 시스템이 현재의 ID를 잊어버리기 때문에 더 이상 설계된 대로 작동하지 않는 API를 중단하는 획기적인 변화를 도입했습니다.캐시가 있든 없든 이는 용납할 수 없으며, Bryan이라는 이름의 마이크로소프트 개발자는 캐시가 "설계상"이며 "기능"임을 세상에 알리는 대신 소유해야 합니다.물론 캐싱도 기능이지만, 다음 아이덴티티가 무엇인지 추적할 수 없게 되는 것은 기능이 아닙니다. 이것은 골치 아픈 문제입니다.

내 DB가 공유 호스팅 서버에 있기 때문에 사용한 해결 방법을 공유할 것이며, 기본 키 열을 삭제하고 다시 만들지 않을 것입니다. 이는 엄청난 PITA가 될 것입니다.

대신, 이것은 나의 부끄러운 해킹입니다(그러나 마이크로소프트가 도입한 이 POS 버그만큼 부끄러운 것은 아닙니다).

해킹/수정:

삽입 명령 전에 각 삽입 전에 ID를 다시 설정하십시오.이 수정은 SQL Server 인스턴스에 대한 관리자 제어 권한이 없는 경우에만 권장되며, 그렇지 않은 경우 서버를 다시 시작할 때 다시 시드하는 것이 좋습니다.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

삽입하기 바로 전에 그 세 줄만 사용하면 됩니다. 그리고 당신은 가야 합니다.성능에 큰 영향을 미치지 않습니다. 즉, 눈에 띄지 않을 것입니다.

행운을 빌어요.

ID 값을 건너뛰는 데는 여러 가지 이유가 있을 수 있습니다.롤백 삽입에서부터 복제를 위한 ID 관리에 이르기까지 다양합니다.당신의 경우 무엇이 이것을 유발하는지 저는 당신의 시스템에서 시간을 보내지 않고는 알 수 없습니다.

그러나 어떤 경우에도 ID 열을 연속 열로 가정할 수 없습니다.격차를 유발할 수 있는 것들이 너무 많습니다.

이에 대한 자세한 내용은 http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/ 에서 확인할 수 있습니다.

언급URL : https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database