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(id.id) As [table_name]
, id.name As [column_name]
, t.name As [data_type]
, IDENT_CURRENT(Object_Name(id.id)) 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
syscolumns As id
Join systypes As t On id.xtype = t.xtype
Where
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!
