Identity columns are widely used as primary key columns in SQL Server tables. They can be one of the data types TINYINT, SMALLINT, INT, BIGINT, NUMERIC or DECIMAL. Each of these data types has an upper limit and when that upper limit is reached, an error is raised and further inserts into the table are impossible.
The error that is raised looks like the following:
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
Max allowed values for the accepted data types are:
|Data Type||Max Value|
For decimal/numeric data types, the max value (38 digits) results from the max precision when the scale is 0, that is NUMERIC(38,0). If you try to use a scale other than 0, you’ll get:
Identity column ‘Id’ must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
List columns that may overflow
So. Here is a script that will iterate through all databases on an instance and list all columns that are about to overflow. The exact definition of “about to overflow” is that the current identity value is more than 90 % of the max for the data type. But you can easily change that in the next to last row in the script.
The script is based on SQL Server metadata, so it is not particularly heavy to run. But test it first.
IF OBJECT_ID('tempdb..#OverflowColumns') IS NOT NULL BEGIN DROP TABLE #OverflowColumns END CREATE TABLE #OverflowColumns ( DatabaseName SYSNAME NOT NULL, TableName SYSNAME NOT NULL, ColumnName SYSNAME NOT NULL, DataType SYSNAME NOT NULL, IdentCurrent NUMERIC(38,0) NULL, PercentOfMax NUMERIC(10,2) NULL ) EXECUTE sp_MSforeachdb 'USE [?] ;WITH CTE_DT AS ( SELECT C.object_id, S.name + ''.'' + SO.name AS TableName, C.name AS ColumnName, C.user_type_id, C.precision, T.name AS DataType, IDENT_CURRENT(S.name + ''.'' + SO.name) AS IdentCurrent FROM sys.columns C JOIN sys.objects SO ON C.object_id = SO.object_id AND SO.type = ''U'' JOIN sys.schemas S ON SO.schema_id = S.schema_id JOIN sys.types T ON C.user_type_id = T.user_type_id WHERE C.is_identity = 1 ) ,CTE_DT2 AS ( SELECT object_id, TableName, ColumnName, DataType, IdentCurrent, CASE WHEN user_type_id = 48 THEN IdentCurrent / 255 WHEN user_type_id = 52 THEN IdentCurrent / 32767 WHEN user_type_id = 56 THEN IdentCurrent / 2147483647 WHEN user_type_id = 127 THEN IdentCurrent / 9223372036854775807 WHEN user_type_id IN (106,108) THEN IdentCurrent / CAST(REPLICATE(''9'', precision) AS NUMERIC(38,0)) END AS PercentOfMax FROM CTE_DT ) INSERT #OverflowColumns (DatabaseName, TableName, ColumnName, DataType, IdentCurrent, PercentOfMax) SELECT DB_NAME(), TableName, ColumnName, DataType, IdentCurrent, PercentOfMax FROM CTE_DT2 WHERE PercentOfMax > 0.9 ' SELECT * FROM #OverflowColumns
The results might look like this for a tinyint identity column that is about to overflow since the current identity value is 255:
If you find columns that are about to overflow, you are very lucky if they aren’t part of a primary- foreign key relation. If they are, and you try to change the data type, you may run into the following errors:
The object ‘pkId’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.
How to solve that problem depends heavily on your system, but perhaps one method would be to drop all constraints before the data type change and then re-create constraints. But again, that depends on if it is safe to do that in your system.