Heading towards an identity crisis?

My client recently struck an issue with an identity column value. Basically they ran out! This brought a very visible (read customer facing) interruption to one of their systems.

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.