I use identity columns frequently. After all, identity columns make great clustering keys. But it’s important when using identity columns to check on the amount of values you have left before you reach the limit of your data type. An identity column has a fixed amount of values it can use based upon whether you specified tinyint, smallint, int, or bigint when you defined the column. If you reach this limit, your inserts will blow up and cause a Chernobyl-like SQL meltdown will begin to fail. I just finished an audit of my tables and thought I’d share the script. I would like to warn that this script is *not* perfect… namely, it doesn’t handle negative integer values very elegantly. It also doesn’t know if you started your seed at zero, approached your max positive limit, then reseeded to the negative limit (see my “quick and dirty fix” tip at the end of this article).
/* Define how close we are to the value limit before we start throwing up the red flag. The higher the value, the closer to the limit. */ Declare @threshold decimal(3,2) = .85; /* Create a temp table */ Create Table #identityStatus ( database_name varchar(128) , table_name varchar(128) , column_name varchar(128) , data_type varchar(128) , last_value bigint , max_value bigint ); /* Use an undocumented command to run a SQL statement in each database on a server */ Execute sp_msforeachdb ' Use [?]; Insert Into #identityStatus Select ''?'' As [database_name] , Object_Name(id.object_id, DB_ID(''?'')) As [table_name] , id.name As [column_name] , t.name As [data_type] , Cast(id.last_value As bigint) As [last_value] , Case When t.name = ''tinyint'' Then 255 When t.name = ''smallint'' Then 32767 When t.name = ''int'' Then 2147483647 When t.name = ''bigint'' Then 9223372036854775807 End As [max_value] From sys.identity_columns As id Join sys.types As t On id.system_type_id = t.system_type_id Where id.last_value Is Not Null'; /* Retrieve our results and format it all prettily */ Select database_name , table_name , column_name , data_type , last_value , Case When last_value < 0 Then 100 Else (1 - Cast(last_value As float(4)) / max_value) * 100 End As [percentLeft] , Case When Cast(last_value As float(4)) / max_value >= @threshold Then 'warning: approaching max limit' Else 'okay' End As [id_status] From #identityStatus Order By percentLeft; /* Clean up after ourselves */ Drop Table #identityStatus; |
If you find yourself quickly approaching your max limit and need to implement a quick and dirty fix, you can reseed your identity column. Of course, this only works if you started at zero instead of the actual lower, negative limit.
