It’s quite common for an Identity column to be configured as an INT data type and the business is impacted when it fills-up.
Ideally the column would be changed to the BIGINT data type and the issue is resolved; however, sometimes this change is not quickly possible due to other objects using the column and/or resource constraints i.e. indexes, available disk space etc., and you have to stick with INT and transition to BIGINT at a later stage when possible.
If you need to stay with INT, the common solution is to start the reseed at -2,147,483,648 and let it work back to 0.
A test example for you is shown below:
/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident;
/* reseed from -2,147,483,648 working back to 0*/
DBCC CHECKIDENT ('test_ident', RESEED, -2147483648);
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident;
/* drop test table */
DROP TABLE test_ident;Below is an example of changing the Identity column to the BIGINT data type, if suitable for your scenario.
NOTE: A schema modify (SCH-M) lock is required on the table while the ALTER command runs.
/* create test table */
CREATE TABLE test_ident (c1 int IDENTITY (2147483647,1), c2 float);
/* try insert some records and only 1 will succeed*/
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view record */
SELECT * FROM test_ident
/* change indentity column to bigint */
ALTER TABLE test_ident ALTER COLUMN c1 bigint;
/* insert some records */
INSERT INTO test_ident (c2) SELECT RAND();
GO 5
/* view records */
SELECT * FROM test_ident
/* drop test table */
DROP TABLE test_ident;NOTE: If you use SSMS to change the column type, it will drop and re-create the table. Therefore, rather use the ALTER TABLE command.
Another consideration is the impact that the reseed will have on your sort order.
Below demonstrates the effect of using a negative increasing reseed vs a negative decreasing reseed and if you need to emulate the BIGINT data type i.e. show values larger than 2,147,483,647.
/* reseed starting from -2147483648 and increasing */
DECLARE @t1 TABLE (ident int, inserted int);
INSERT INTO @t1 (ident,inserted) VALUES (1,1);
INSERT INTO @t1 (ident,inserted) VALUES (2,2);
INSERT INTO @t1 (ident,inserted) VALUES (3,3);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483648,4);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483647,5);
INSERT INTO @t1 (ident,inserted) VALUES (-2147483646,6);
SELECT
ident
,inserted AS reseed_increasing
,CASE WHEN ident < 0 THEN ((2147483648 * 2) + CAST(ident AS bigint)) ELSE ident END AS emulate_bigint
FROM @t1 ORDER BY ident ASC;
/* reseed starting from -1 and decreasing */
DECLARE @t2 TABLE (ident int, inserted int);
INSERT INTO @t2 (ident,inserted) VALUES (1,1);
INSERT INTO @t2 (ident,inserted) VALUES (2,2);
INSERT INTO @t2 (ident,inserted) VALUES (3,3);
INSERT INTO @t2 (ident,inserted) VALUES (-1,4);
INSERT INTO @t2 (ident,inserted) VALUES (-2,5);
INSERT INTO @t2 (ident,inserted) VALUES (-3,6);
SELECT
ident
,inserted AS reseed_decreasing
,CASE WHEN ident < 0 THEN 2147483647 - CAST(ident AS bigint) ELSE ident END AS emulate_bigint
FROM @t2 ORDER BY ident ASC;