Used Identity Fields in SQL

Used Identity Fields in SQL

What are identity fields?

Like most database systems, SQL Server can define numeric fields as Identity fields. This means the database server automatically sets the numeric field value to the next available value each time a record is inserted into the table. The first record gets 1, the second 2, etc.

Typically, the developers create Identity fields as the Primary Key columns on tables. Again, typically, these are ‘int’ fields.

In most databases, this does not cause a problem. Indeed, for most databases, the use of ‘int’ Identity fields will never be a problem.

What’s the problem?

The problem is that an ‘int’ field has limits. It can only hold numbers in the range -2,147,483,648 to 2,147,483,647, and most developers, myself included, leave the defaults on the fields in SQL so that the range starts at 1. So, the limit is from 1 to 2,147,483,647.

In most cases, using an ‘int’ Identity field is perfectly fine, and throughout the life of the table, it never gets anywhere this limit. However, as applications become more successful and are used by more users and customers, more traffic/business goes through an application; it is surprising how quickly the table starts getting closer to this limit.

Pre-emptive analysis

In a lot of cases, applications creep towards this limit slowly. In others, it races to the limit.

It is, therefore, important to keep an eye on the used Identity values for the tables in a database. The SQL script below can be run against a database to see how many values have been used and how many remain.

The code

/* 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);
SET @threshold = .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

DECLARE @dbname sysname = 'WintrixGDB';
DECLARE @sql nvarchar(4000);

-- Use an cursor to iterate through the databases since in 2000 there's no sp_MSForEachDB command...
    SET @sql = N'Use [' + @dbname + '];
    Insert Into #identityStatus
    Select ''' + @dbname + ''' As [database_name]
        , Object_Name( As [table_name]
        , As [column_name]
        , As [data_type]
        , IDENT_CURRENT(Object_Name( As [last_value]
        , Case
            When = ''tinyint''   Then 255
            When = ''smallint''  Then 32767
            When = ''int''       Then 2147483647
            When = ''bigint''    Then 9223372036854775807
          End As [max_value]
        syscolumns As id
        Join systypes As t On id.xtype = t.xtype
        id.colstat&1 = 1    -- this identifies the identity columns (as far as I know)

    EXECUTE sp_executesql @sql;

/* 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;

When this runs, it lists the tables with the least free identity values first – those are the ones you will need to do something about first!


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.