Can your application handle all BIGINT values?

January 3, 2023

Can your application handle all BIGINT values?

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 12:24 pm

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.

-- Comments (4)

4 Comments »

[…] 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:

DROP TABLE IF EXISTS TableA
 
CREATE TABLE TableA (
--    id BIGINT IDENTITY(-9223372036854775808, 1) 
    id BIGINT IDENTITY(1, 1) 
        constraint pk_TableA primary key, 
    filler BIGINT NOT NULL
);
 
INSERT INTO TableA (filler)
SELECT value FROM GENERATE_SERIES(1, 100000);
 
ALTER INDEX pk_TableA ON TableA REBUILD WITH (DATA_COMPRESSION = ROW)
 
exec sp_spaceused 'TableA';
 
/* results 
                    name    rows    reserved  data     index_size  unused
start at min value  TableA  100000  1672 KB   1552 KB  16 KB       104 KB
start at 1          TableA  100000  1160 KB   1088 KB  16 KB       56 KB
*/

Comment by Michael J. Swart — August 14, 2023 @ 2:02 pm

RSS feed for comments on this post. TrackBack URL

Michael J. Swart

January 3, 2023