SQL Server – How to Reduce Identity Values to Avoid Integer Overflow

identitysql serversql server 2014

I have some leaf tables (no FK towards them) with a few hundreds of thousands records that are used to synchronize some external data using Entity Framework ORM. This involves some DELETEs followed by BULK INSERTs.

For most of the tables, some old values may stay forever, so I cannot use SEQUENCEs with CYCLE, as suggested in one of the comments.

One effect is that identity values will keep increasing over time and I want to be able to reduce their values.

This question and its answers explain that identity values cannot be updated, even if identity_insert is on for the table.

One quick way to do is transferring all data to a buffer table and perform the switch via rename. Something like the following:

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);  

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.    

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)    

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.  

-- this should be replaced with commit when not in test mode
rollback

I can afford to make this kind of operations during the night and they seem to take only a few seconds for my data volume (4 seconds for this example).

Question: Are there any methods to avoid performing a full data copy to obtain smaller values for identity columns? Or this is one of the best approaches in my context (reasonable data volume and being able to lock some tables for several seconds).

Best Answer

You could try simply dropping and re-adding the Identity column without copying the entire table. This has the same effect as what you are attempting with the code in the question. Neither approach really handles keeping existing rows as per the "some old values may stay forever" statement from the question, but for whatever situations work with the code in the question, the following should also work.

You should also consider starting the identity range at the min-value. Starting at 1 gives you just over 2.1 billion values, so starting at the minimum value (or close to it) gives you 4.2 billion values. Hence, less likely you will be needing such a reseed operation in the future.

SETUP

-- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

Drop and re-Create Identity PK

BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK TRAN;
  END;

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;
Related Question