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
Great idea, thanks. In DEV we use a SEED for each table’s identity that is widely spaced, so if we accidentally JOIN on an ID that is the wrong Column Name (i.e. a column name for an IDENTITY but in a different table) then the JOIN will fail with zero rows – to bring to light that particular type of coding error in DEV – we set the differential to the next table based on how much test data we will have
So in extending to BIGINT I would want to retain the “interval” between the different tables’ IDENTITY columns, e.g by incrementing by 2147483648 rather than all the tables starting at that, same, point.
Comment by Kris — August 14, 2023 @ 5:56 am
For new tables, I have had some application teams start to use the negative most number of a [bigint] IDENTITY(-9223372036854775808 ,1). This gives us more values to use for the large tables with lots of new record inserts, and allows that application to test a value that might otherwise cause issues years down the line during their first few tests for the example you provided in this post.
Comment by Ian Cameron — August 14, 2023 @ 1:14 pm
Hey Ian,
The only thing that makes me wary of that large negative value is that row compression doesn’t work as nicely. There’s a hidden datatype called vardecimal that is slightly more efficient than ints and bigints if the numbers are small. For example:
Comment by Michael J. Swart — August 14, 2023 @ 2:02 pm