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) Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- IdNum numeric 6 12 0 0 NULL NULL NULL NULL 1 Object does not have any indexes. No defined keys for this object. name type partition_type partitions partition_keys ------- ---------- ---------------- ------------ --------------- mytable base table roundrobin 1 NULL partiton_name partition_id pages segment create_date ----------------- -------------- --------- --------- ------------------------ mytable_1136004047 1136004047 1 default Nov 29 2004 1:30PM partition_conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ration(Min/Avg) ----------- ----------- ----------- --------------------- ------------------- 1 1 1 1.000000 1.000000
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 optimistic_index_lock dealloc_first_txtpg --------------------------- ----------------------- -------------------- 0 0 0 (return status = 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 Object_type Create_date ------- -------- -------------- ----------------------------- mytable dbo user table Nov 29 2004 1:30PM (1 row affected) Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity --------------- --------------- ------ ---- ----- ----- --------------- --------------- -------- IdNum numeric 6 12 0 0 NULL NULL NULL NULL 1 Object does not have any indexes. No defined keys for this object. name type partition_type partitions partition_keys ------- ---------- ---------------- ------------ --------------- mytable base table roundrobin 1 NULL partiton_name partition_id pages segment create_date ----------------- -------------- --------- --------- ------------------------ mytable_1136004047 1136004047 1 default Nov 29 2004 1:30PM partition_conditions -------------------- NULL Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ration(Min/Avg) ----------- ----------- ----------- --------------------- ------------------- 1 1 1 1.000000 1.000000 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 optimistic_index_lock dealloc_first_txtpg --------------------------- ----------------------- -------------------- 0 0 0 (return status = 0)
