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.
[…] Michael J. Swart reminds us that it’s not just the database which needs to be able to handle l…: […]
Pingback by Testing BIGINT Support in Applications – Curated SQL — January 4, 2023 @ 8:00 am