開啟IDENTITY_INSERT選項Insert資料時會影響其他Session對相同資料表的Insert嗎?

2018-07-21

3772
0
[SQL]TSQL語法技巧
2018-07-19

近期在撰寫TSQL做資料匯入時，會因為目的資料表有Identity型態的欄位，所以得在該資料表開啟IDENTITY_INSERT後再寫入資料。

我們在用匯入匯出精靈來匯入資料時會針對資料表目的欄位型態有Identity來勾選[啟用識別插入]，這樣一來我們就可以將來源資料表的值寫入到設定為Identity的目的欄位了(如下圖所示)。

然而有時候在Procerdue中我們會有相同的需求，此時就得在SP的TSQL中用SET IDENTITY_INSERT來對資料表動一點手腳，讓我們可以寫值到Identity的欄位內。

然而我們在SP中對資料表設定SET IDENTITY_INSERT ON後，萬一有其他Session也剛好也同時對這一個資料表做Insert，那SP中的IDENTITY_INSERT設定會影響到另一個Session嗎?

為釐清這一個問題所以我做一個簡易測試，下面程式碼中我建立一個資料表叫tbInsert其id欄位是identity primary key clustered。我在這一個Session中開啟tbInsert資料表的IDENTITY_INSERT功能後，每10ms寫入一筆資料。注意 !我寫入資料時有給值Insert進id欄位，而name欄位給的字串是rock。(如下面的code，完全成功的話這一個Session會寫入999筆資料)

create table tbInsert(id int identity primary key clustered,name char(10))
go

set nocount on;
set identity_insert tbInsert on;--開啟tbInsert資料表的Identity Insert功能
go

declare @i int
set @i=1
while @i<1000
begin
	waitfor delay '00:00:00.010' --10ms寫入一筆
	insert into tbInsert(id,name) values(@i,'rock')
	set @i+=1;
end

而第二個Session的code如下，這一個Session沒有開啟tbInsert資料表的IDENTITY_INSERT功能，每20ms寫入一筆資料，資料中只有給name欄位值carey而已，id欄位看看會不會不受另一個Session影響而順利自動取號。(如下面的code，完全成功的話這一個Session會寫入499筆資料)

set nocount on;
go

declare @i int
set @i=1
while @i<500
begin
	waitfor delay '00:00:00.020'--20ms寫入一筆
	insert into tbInsert(name) values('carey')
	set @i+=1;
end