Michael J. Swart

In the past I’ve written about monitoring identity columns to ensure there’s room to grow.

But there’s a related danger that’s a little more subtle. Say you have a table whose identity column is an 8-byte bigint. An application that converts those values to a 4-byte integer will not always fail! Those applications will only fail if the value is larger than 2,147,483,647.

If the conversion of a large value is done in C#, you’ll get an Overflow Exception or an Invalid Cast Exception and if the conversion is done in SQL Server you’ll see get this error message:

Msg 8115, Level 16, State 2, Line 21
Arithmetic overflow error converting expression to data type int.

The danger

If such conversions exist in your application, you won’t see any problems until the bigint identity values are larger than 2,147,483,647. My advice then is to test your application with large identity values in a test environment. But how?

Use this script to set large values on BIGINT identity columns

On a test server, run this script to get commands to adjust bigint identity values to beyond the maximum value of an integer:

-- increase bigint identity columns
select 
	'DBCC CHECKIDENT(''' + 
	QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
	QUOTENAME(object_Name(object_id)) + ''', RESEED, 2147483648);
' as script
from 
	sys.identity_columns
where 
	system_type_id = 127
	and object_id in (select object_id from sys.tables);
 
-- increase bigint sequences
select 
	'ALTER SEQUENCE ' +
	QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' +
	QUOTENAME(object_Name(object_id)) + ' 
	RESTART WITH 2147483648 INCREMENT BY ' + 
	CAST(increment as sysname) +
	' NO MINVALUE NO MAXVALUE;
' as script
from 
	sys.sequences
where 
	system_type_id = 127;

Prepared for testing

The identity columns in your test database are now prepared for testing. And hopefully you have an automated way to exercise your application code to find sneaky conversions to 4-byte integers. I found several of these hidden defects myself and I’m really glad I had the opportunity to tackle these before they became an issue in production.