Sql-Server

什麼可能導致插入錯誤的 ID?

  • April 21, 2016

我有一個 SQL Server 2008 伺服器(內部版本 10.0.5500)。本週早些時候,我在一個已經有數據的表上執行了這個:

delete from dbo.table
go    
dbcc checkident('dbo.table',reseed,0)

當使用者稍後去創建一條新記錄時,不知何故,ID 0 被插入到 ID 列中,而不是 1 SQL Server 通常放入如果為 ID 配置了 identity(1,1)。

這引起了一些奇怪的問題,但清除數據並執行重新種子會導致插入 1,正如預期的那樣。我無法複製這個問題。

作為參考,這是我們保存 sp 的一般格式:

alter procedure dbo._TableSave
   @pk_id int,
   @field varchar(50)
as
   if (@pk_id is null)
   begin
       set nocount on;

       insert into dbo.Table
       (
           Field
       )
       values
       (
           @field
       );
       select scope_identity();
   end
   else
   begin
       update dbo.Table
       set Field=@field
       where PK_ID=@pk_id

       select @pk_id
   end

有誰知道什麼會導致 SQL Server 在 ID 應該是 1 時插入 0?

我們不會在應用程序的任何位置將數據插入到標識列(使用identity_insert)。

這樣做dbcc checkident('dbo.table',reseed,0)將導致新創建/截斷表中的下一個條目具有 0 作為標識。

       CREATE TABLE TestIdent
       (
           ID INT NOT NULL CONSTRAINT PK_TestIdent PRIMARY KEY CLUSTERED IDENTITY(1,1)
           , SomeText nvarchar(255)
       );

       dbcc checkident('dbo.TestIdent',reseed,0)

       INSERT INTO TestIdent VALUES ('Test');

       SELECT * FROM dbo.TestIdent;

在此處輸入圖像描述

這會導致輸入的下一個身份為 10:

       TRUNCATE TABLE dbo.TestIdent;

       DBCC CHECKIDENT('dbo.TestIdent',reseed,10)

       INSERT INTO TestIdent VALUES ('Test');

       SELECT * FROM dbo.TestIdent;

在此處輸入圖像描述

我建議使用以下程式碼來防止它發生:(

而不是您在問題中發布的程式碼)

DELETE FROM dbo.table
GO
IF EXISTS 
(
   SELECT * 
   FROM sys.identity_columns 
   WHERE object_id = OBJECT_ID('dbo.table') 
       AND last_value IS NOT NULL
)
DBCC CHECKIDENT ('dbo.table', RESEED, 0);

它應該防止“重新播種到 0”。

說明:(

我沒有找到支持它

的官方文件……)問題出sys.syscolpars在名為列的表中idtval

,該列包含所有標識列資訊。

由於該表在數據庫中的每一列(不僅是標識列)都有一行,因此該欄位中

NULL值將表示一個非標識列

(與sys.identity_columns用於NULL新\截斷表不同)。

sys.syscolpars使用不同的方法來保存資訊:

它使用0x01000000010000000100000001格式(新創建的 identity(1,1) 範例),

左側 8 位數字(位置 1-8 之後0x)為Last_Value

接下來的 8 位數字(位置 9-16在0x)for之後increment_value

接下來的 8 位數字(在0x)for之後的位置 17-24 seed_value

右邊的第二個數字對我來說是個謎,右邊

的第一個數字 - 告訴我們它是否是一個新表(=1 ) 或不!

回到我們的問題 -執行

DBCC CHECKIDENT,它會檢查sys.identity_columns.last_value

是否是,NULL然後在正確的位置更新sys.syscolpars.idtvalwith的新值。仍然。 這導致下一個插入標識值被視為在新表中。 (我假設是獨立的,因為第一個立即更新,而後者僅在執行檢查點後更新)。 1

sys.identity_columns.last_value``NULL

sys.identity_columns``sys.syscolpars

由於無法sys.syscolpars實時查詢(需要DAC),

我的建議是上面的條件,可能加上ELSE子句。

再說一次,我不能保證這些解釋中的任何一個都是真的,但它確實有效:)

如果有人有更準確的DBCC CHECKIDENT後台操作資訊,

我很想學習,請分享!

祝你好運,

羅伊·加維什

引用自:https://dba.stackexchange.com/questions/39824