Display Table-Specific Identity Gap Information

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    Object_type    Create_date
------- -------- -------------- -----------------------------
mytable      dbo     user table            Nov 29 2004 1:30PM

(1 row affected)
. . . 
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap 
------------ -------------- ---------- ----------------- ------------ 
           1              0          0                 0            0

If you change the identity_gap of mytable to 20, 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    Object_type    Create_date
------- -------- -------------- -----------------------------
mytable dbo      user table     Nov 29 2004 1:30PM

(1 row affected)
. . . 
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap 
------------ -------------- ---------- ----------------- ------------
           1              0          0                 0           20