To see the identity_gap setting for a table, use sp_help.
For example, the zero value in the identity_gap column (towards the end of the output) indicates that no table-specific identity gap is set. mytable uses the server-wide identity burning set factor value.
sp_help mytable
Name Owner
Type
------------------------------ ------------------------------
----------------------
mytable dbo
user table
(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Jun 8 1999 5:35PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Identity
--------------- --------------- ------ ---- ----- ----- ---------------
--------------- --------
idnum numeric 6 12 0 0 NULL
NULL 1
Object does not have any indexes.
No defined keys for this object. Object is not partitioned. Lock scheme Allpages The attribute ’exp_row_size’ is not applicable to tables with allpages lock scheme. The attribute ’concurrency_opt_threshold’ is not applicable to tables with allpages lock scheme. exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
1 0 0 0 0
concurrency_opt_threshold
-------------------------
0
If you change the identity_gap of mytable to 20, the sp_help output for the table shows 20 in the identity_gap column. This setting overrides the server-wide identity burning set factor value.
sp_help mytable
Name Owner
Type
------------------------------ ------------------------------
----------------------
mytable dbo
user table
(1 row affected)
Data_located_on_segment When_created
------------------------------ --------------------------
default Jun 8 1999 5:35PM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Identity
--------------- --------------- ------ ---- ----- ----- ---------------
--------------- --------
idnum numeric 6 12 0 0 NULL
NULL 1
Object does not have any indexes.
No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute ’exp_row_size’ is not applicable to tables with
allpages lock scheme.
The attribute ’concurrency_opt_threshold’ is not applicable to
tables with allpages lock scheme.
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
1 0 0 0 20
concurrency_opt_threshold
-------------------------
0
