I'm sure many of you are in the situation where you have defined an identity column for your primary key. There's nothing particularly wrong with that but do you keep an eye on whether you're near to running out of values or not?
Simply run this script to find out:
with cteIdentity as ( -- Basic identity info select o.object_id as TableID ,c.column_id as ColumnID ,s.name + '.' + o.name as TableName ,ident_current(s.name + '.' + o.name) as CurrentIdentity ,power(2.0, ((8 * c.max_length) - 1)) - 1 as MaxIdentity from sys.objects as o inner join sys.columns as c on o.object_id = c.object_id inner join sys.schemas as s on o.schema_id = s.schema_id where c.is_identity = 1 -- Table has an identity column and o.type = 'U' -- User tables only ) -- Calc the percentage used select TableName ,CurrentIdentity ,(CurrentIdentity / MaxIdentity) * 100 as PercentageOfMax from cteIdentity;
It would be very easy to wrap some alerts around the results if the "PercentageOfMax" value is above a predefined threshold level (e.g: 95%)
Naturally this does not fix the issue you might be screaming towards, but it gives you that important "heads up" that you need to start planning for a change soon.
Consider that to increase the data type to bigint from an int (which is what you would typically have to do) could involve:
- An outage to perform the table change
- The need to re-factor several (many?) procedures that reference the table
- Since its likely the identity is a primary key you're going to have to amend any tables that reference this one
- You'll need to release an application update to cater for the increase in data-type size
Trust me; its far easier to address this before it happens than during an identity crisis.