什麼可能導致插入錯誤的 ID?
我有一個 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-24seed_value
,右邊的第二個數字對我來說是個謎,右邊
的第一個數字 - 告訴我們它是否是一個新表(=1 ) 或不!
回到我們的問題 -執行
時
DBCC CHECKIDENT
,它會檢查sys.identity_columns.last_value
是否是,
NULL
然後在正確的位置更新sys.syscolpars.idtval
with的新值。仍然。 這導致下一個插入標識值被視為在新表中。 (我假設是獨立的,因為第一個立即更新,而後者僅在執行檢查點後更新)。1
sys.identity_columns.last_value``NULL
sys.identity_columns``sys.syscolpars
由於無法
sys.syscolpars
實時查詢(需要DAC),我的建議是上面的條件,可能加上
ELSE
子句。再說一次,我不能保證這些解釋中的任何一個都是真的,但它確實有效:)
如果有人有更準確的
DBCC CHECKIDENT
後台操作資訊,我很想學習,請分享!
祝你好運,
羅伊·加維什