SET IDENTITY_INSERT xyz ON 的范围是什么? [英] What is the scope of SET IDENTITY_INSERT xyz ON?
问题描述
SET IDENTITY_INSERT xyz ON
的范围是什么?
如果我在某个表的一个存储过程中使用它,如果运行不同过程的不同用户同时插入到该表中,会发生什么情况?
If I use this in one stored procedure for a certain table, what happens if a different user running a different procedure, inserts into that certain table at the same time?
另外,如果不同的用户/程序尝试设置 SET IDENTITY_INSERT xyz ON
同时为不同的表?
Also, what happens if different users/procedures try to set SET IDENTITY_INSERT xyz ON
for different tables at the same time?
推荐答案
这是一个会话选项,一张桌子一次只能为任何一张桌子打开这个选项,但多个不同的会话可以为同一张桌子打开这个选项(不是不过这肯定是个好主意!)
It's a session option a table can only have the option on for any one table at a time but multiple different sessions can have it on for the same table (not sure that would ever be a good idea though!)
当子批次完成(设置此选项)时,它看起来会自动取消连接设置.
When a child batch finishes (that sets this option) it looks like it automatically gets unset for the connection.
CREATE TABLE Tst
(C INT IDENTITY(1,1))
EXEC('SET IDENTITY_INSERT Tst ON')
INSERT INTO Tst(C) VALUES (1) /*Fails - Complains IDENTITY_INSERT is off*/
SET IDENTITY_INSERT Tst ON
EXEC('INSERT INTO Tst(C) VALUES (1)') /*Succeeds this way round*/
SET IDENTITY_INSERT Tst OFF
SET IDENTITY_INSERT Tst ON
EXEC('SET IDENTITY_INSERT Tst ON; INSERT INTO Tst(C) VALUES (1);') /* Also succeeds like this*/
这篇关于SET IDENTITY_INSERT xyz ON 的范围是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!