Projecting growth can be hard. Initially, it may seem like using INT for something like an identity column will be enough to last forever.
I remember getting our first or second PC in the 1990s and being told “this hard drive is so big you’ll be set for life!” Pretty sure that was around 1 GB. 20 years ago that wasn’t so bad but it doesn’t cover much today.
What are your options if that INT isn’t cutting it anymore? Let’s go through some examples.
First, let’s create a table and load it with data so we can test. If we’re going through the scenario where we run out of INT values in an identity column, we should probably load it up with a couple billion values, right?
We can use the following script to reseed the identity column to a value of our choosing:
DBCC CHECKIDENT ('TableName', RESEED, ###);
GO
This will make it easier on us to run out of INT values Let’s setup a test table and populate it:
CREATE TABLE IdentTest
(
id INT identity(1, 1) NOT NULL
,TestData VARCHAR(20) NOT NULL
);
GO
DBCC CHECKIDENT ('IdentTest', RESEED, 2147483638);
GO
DECLARE @IntCount AS SMALLINT = 10
WHILE @IntCount > 0
BEGIN
INSERT INTO IdentTest
VALUES ('This is a test')
SET @IntCount -= 1
END
Looking at our table, we see we’re at the end of the line for our INT id column:
If we try to add one more record, we’ll get an error:
Msg 8115, Level 16, State 1, Line 14
Arithmetic overflow error converting IDENTITY to data type int.
What next? Add New BIGINT Column and Update?
We can try adding a new BIGINT column to the table, move our data in the id column to the new column, and then drop the original column. Seems like that would work but…
ALTER TABLE IdentTest ADD BigINTID BIGINT IDENTITY(1,1);
GO
Msg 2744, Level 16, State 2, Line 23
Multiple identity columns specified for table ‘IdentTest’. Only one identity column per table is allowed.
The error message is clear. That’s not going to work. What if we add a temporary column to move the data to, drop the identity column, and then update the identity BIGINT column with the temp column values? We’re good until we go to update BigINTID:
ALTER TABLE IdentTest ADD TempID INT;
GO
UPDATE IdentTest SET TempID=ID;
GO
ALTER TABLE IdentTest DROP COLUMN ID;
GO
ALTER TABLE IdentTest ADD BigINTID BIGINT IDENTITY(1,1);
GO
SET IDENTITY_INSERT dbo.IdentTest ON;
GO
UPDATE IdentTest SET BigINTID=TempID;
GO
SET IDENTITY_INSERT dbo.IdentTest OFF;
GO
Msg 8102, Level 16, State 1, Line 36
Cannot update identity column ‘BigINTID’.
Let’s check out some of our other options.
Time to Create a New Table
I think the easiest way is by creating a new table but including the BIGINT data type instead of INT:
CREATE TABLE BigIntTest
(
ID BIGINT identity(1, 1) NOT NULL
,TestData VARCHAR(20) NOT NULL
);
GO
Insert the data from our old table into the new:
SET IDENTITY_INSERT BigIntTest ON;
INSERT INTO BigIntTest (ID, TestData)
SELECT ID, TestData
FROM IdentTest
SET IDENTITY_INSERT BigIntTest OFF;
We’ll want to RESEED again so that our id values pick up where they left off:
DBCC CHECKIDENT ('BigIntTest', RESEED, 2147483647)
Drop the old table and rename our new table to match the old table name:
DROP TABLE IdentTest;
GO
EXEC sp_rename 'dbo.BigIntTest', 'IdentTest';
GO
Now, we can add some new values to test again and confirm that we’re good to go.
Negative can be Positive
Another possible option to consider if you run out of INT values is to RESEED and go negative. For example:
DBCC CHECKIDENT ('IdentTest', RESEED, -2147483647);
GO
If we chose this route instead of switching to BIGINT and added new values, we’d see the id value start to move towards 0:
This isn’t something I would personally choose to do but it’s a nice emergency option to consider.
Simple but not Easy
Keep an eye on your data and hopefully this issue won’t sneak up on you.
Thanks for reading!
Chad Callihan 