How SQL Server resets the value of the Identity identity column

I. BACKGROUND

The ID field in table A in the SQL Server database is defined as: [ID] [int] IDENTITY (1,1), and as the data grows, the ID value is close to 2147483647 (int's range is:-2 147 483 648 to 2 147 483 647) , although the old data has been archived, but this table needs to keep the most recent 100 million data, what is the method to solve the problem of the explosion of ID value?

There are two ways to solve the problem: one is to modify the table structure, to modify the int data type of ID to bigint, and the second to reset the value of the ID (Identity identity column) to make it grow again.

Current IDENTITY value: This ID value, which is used to record and save the last time the system allocation was assigned; the next assignment ID is: current identity value + identity increment (usually +1 or set yourself);

Current column value: This is the maximum value of the ID value so far;

Second, the Reset process

(a) in the following test reset identity identity column, first use the following SQL to create a test table:

--Creating a test table create
table [dbo].[ Test_identity] (
    [Identityid] [int] Identity (1,1) not NULL,
    [Name] [nchar] (a) null,
 CONSTRAINT [Pk_testid ] PRIMARY KEY CLUSTERED 
(
    [Identityid] ASC
) with (pad_index  = off, statistics_norecompute  = off, Ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [PRIMARY]
) on [PRIMARY]

(b) display the Insert ID value, insert the record in the following table [Test_identity] as shown in Figure1, and then implicitly insert the ID value, as shown in the record in the following table [Test_identity] as Figure2.

--Display Insert ID value
SET identity_insert [test_identity] on
insert INTO [test_identity] (identityid,name)
SELECT 1000, ' name1 '
SET identity_insert [test_identity] off
    
--Inserts an ID value implicitly insert INTO
[test_identity] (Name)
SELECT ' name2 '

(Figure1: Data logging)

(Figure2: Data logging)

(iii) DBCC checkident (' table_name ', noreseed) does not reset the current identity value. DBCC Checkident Returns a report that indicates the current identity value and the expected identity value. The following SQL statement is executed, and the information returned indicates: The current identity value ' 1001 ', the current column value ' 1001 ', as shown in Figure2.

--Query identity value
DBCC checkident (' test_identity ', noreseed)/
*
Check identity information: Current identity value ' 1001 ', current column value ' 1001 '.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
*/

(iv) implicitly insert the ID value and insert the record in the following table [Test_identity] as shown in Figure3. So executing the SQL statement above does not reset the current identity value and can be safely executed.

--implicitly inserts an ID value
INSERT INTO [test_identity] (Name)
SELECT ' Name3 '

(Figure3: Data logging)

--Query identity value
DBCC checkident (' test_identity ', noreseed)/
*
Check identity information: Current identity value ' 1002 ', current column value ' 1002 '.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
*/