Kaz on Commerce!

When using an int as an identity column in Microsoft SQL Server you’re bound to an upper limit of 2,147,483,647. Ordinarily, that limit would be extremely difficult to exceed e.g. if you had a ShoppingCarts table and it had an int IDENTITY field as the primary key and there were 10,000 carts created a day it would take 588 years to reach this limit.

But if for some reason you do reach this limit you will get error: “Arithmetic overflow error converting IDENTITY to data type int.”

Lets setup some test structures and data:

CREATE TABLE [dbo].[ShoppingCarts](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [Name] nvarchar(100),
 CONSTRAINT [PK_dbo.ShoppingCarts] PRIMARY KEY CLUSTERED ([Id] ASC))
GO

CREATE TABLE [dbo].[ShoppingCartItems](
    [ShoppingCartId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL
 CONSTRAINT [PK_dbo.ShoppingCartItems] PRIMARY KEY CLUSTERED ([ShoppingCartId] ASC, [ProductId] ASC))
GO

ALTER TABLE [dbo].[ShoppingCartItems] ADD  CONSTRAINT [FK_dbo.ShoppingCartItems_dbo.ShoppingCarts_ShoppingCartId] FOREIGN KEY([ShoppingCartId])
REFERENCES [dbo].[ShoppingCarts] ([Id])
GO

-- Reseed ShoppingCarts table so next inserted IDENTITY is 2147383648
DECLARE @Val int = 2147383647
DBCC CHECKIDENT ('ShoppingCarts', RESEED, @Val)

-- Attempts 100,001 inserts, 100,000 are successful but the last insert fails 
-- with the 'Arithmetic overflow error converting IDENTITY to data type int.'
WHILE 1=1
BEGIN
	INSERT INTO [dbo].[ShoppingCarts] ([UserId], [Name]) VALUES (1, 'Name')
	INSERT INTO [dbo].[ShoppingCartItems] ([ShoppingCartId], [ProductId], [Quantity]) VALUES (SCOPE_IDENTITY(), 1, 1)

	SET @Val = @Val + 1
END 

Now lets run through some code that can be used if you run into the “Arithmetic overflow error converting IDENTITY to data type int.” error. In short, the below code will reorganise the IDENTITY column to allow further records to be added.

-- Disable all constraints in the database.
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Copy records to temp table with new ids.
SELECT *,  rank() over (order by Id) as NewId
INTO #TempShoppingCarts
FROM [dbo].[ShoppingCarts]

-- Update linked tables with new id values.
UPDATE sci
SET sci.ShoppingCartId = tsc.NewId
FROM [dbo].[ShoppingCartItems] sci
INNER JOIN #TempShoppingCarts tsc
ON sci.ShoppingCartId = tsc.Id

-- Delete old records (we can't update an IDENTITY column).
DELETE FROM [dbo].[ShoppingCarts]

-- Allow inserting of IDENTITY field by insert statements (not allowed by default).
SET IDENTITY_INSERT [dbo].[ShoppingCarts] ON

-- Copy records back to orginal table with new ids. 
INSERT INTO [dbo].[ShoppingCarts] ([Id], [UserId], [Name])
SELECT [NewId], [UserId], [Name]
FROM #TempShoppingCarts

-- Reseed to the current max id
DECLARE @Max_Identity int 
SELECT @Max_Identity = MAX([Id]) FROM [dbo].[ShoppingCarts]
DBCC CHECKIDENT ('ShoppingCarts', RESEED, @Max_Identity)

-- Clean up
SET IDENTITY_INSERT [dbo].[ShoppingCarts] OFF
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
DROP TABLE #TempShoppingCarts

Run this to check what the current IDENEITY value is for all tables in the database with an IDENTITY column. If you are concerned about hitting the limit it would be a good idea to turn the below query into a scheduled job that alerted you when an IDENTITY column in the database reaches a threshold.

SELECT IDENT_SEED(TABLE_NAME) AS Seed
	,IDENT_INCR(TABLE_NAME) AS Increment
	,IDENT_CURRENT(TABLE_NAME) AS Current_Identity
	,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'