请教关于identity的问题

landhawk

1: 大家都知道一个表有identity字段的话，会在内存中保存一个identity值段，我们叫burning gap或者identity_gap什么的，问题是怎么看当前内存中使用到了哪个呢？比如存放了1-5000的话，当前值是多少呢？
2: 大家知道identity_insert设置成on是为了往表中插入指定的identity值用的，问题是我们怎么知道现在identity_insert值是on呢还是off呢？
3:怎么查看所有现在这个session中set所有参数的值？

谢谢了！

jarjar

1、select @@indentity
2、不知道
3、不知道

2BeSybPro

For question 2:
Create a dummy table and try to turn on identity_insert for the dummy table - if identity_insert is already on for the session, the resulting error message will identify the table.

1> create table test (x numeric(12,0) identity)
2> go
1> create table dummy (x numeric(12,0) identity)
2> go
1> set identity_insert test on
2> go
1> set identity_insert dummy on
2> go

Unable to 'SET IDENTITY_INSERT' for table 'dummy' because IDENTITY_INSERT or IDENTITY_UPDATE is already ON for the table 'test' in database 'tempdb'.

You can also use dbcc pss to find it.

For question 3: sp_configure