Identity Columns: Are You Nearing The Limits?

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]
        , As [column_name]
        , As [data_type]
        , Cast(id.last_value As bigint) As [last_value]
        , Case 
            When = ''tinyint''   Then 255 
            When = ''smallint''  Then 32767 
            When = ''int''       Then 2147483647 
            When = ''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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , . Bookmark the permalink.

7 Responses to Identity Columns: Are You Nearing The Limits?

  1. Attila Farago says:

    the beginning is not working on db names with space or dash
    change to

    Use [?];


  2. Bodhi Densmore says:

    The initial join of sys.identity_columns and sys.types fails by delivering too many rows when the identity column is defined with a UDT. Below is a possibly more robust replacement for that part of the query.
    , Case
    When C.DATA_TYPE = ‘tinyint’ Then 255
    When C.DATA_TYPE = ‘smallint’ Then 32767
    When C.DATA_TYPE = ‘int’ Then 2147483647
    When C.DATA_TYPE = ‘bigint’ Then 9223372036854775807
    When C.DATA_TYPE in (‘numeric’, ‘decimal’)
    then cast(power(cast(10 as float),(C.NUMERIC_PRECISION – C.NUMERIC_SCALE)) AS decimal(26)) -1
    End As [max_value]

  3. Steve Stout says:

    I had to change the first line to:

    DECLARE @threshold DECIMAL(3,2)
    SET @threshold = .85

    As SQL Server 2005 complained ‘Cannot assign a default value to a local variable.’

    Otherwise, it worked great!


  4. Andy Myatt says:

    Hi Michelle

    Great post, thanks for sharing.

    Is there limit to the comment size? I’m trying to post a solution that

    I’ve updated to cater for numeric and decimal identity columns as well as (hopefully) showing free space for identity columns that were seeded from the largest negative value at table creation as well as identity fields that have been re-seeded to the largest negative value after the table was created (as per the workaround above).

    My code assumes that if an identity column has been re-seeded, there has only been one re-seed and that the re-seed value was to the maximum negative value.



  5. Claire says:

    Thanks for the script! And thanks Bodhi for the revision!

  6. Zubair Khan says:

    Brilliant! You are doing great Job 🙂 Hope will learn alot from your articles! Keep it up!

  7. Pingback: Identifying Identity Columns Approaching Their Limit - SQL Server - SQL Server - Toad World

Leave a Reply

Your email address will not be published. Required fields are marked *