SQL Server resets the value of the Identity identity column (int exploded) (reproduced)

First, background

The ID field in table A in the SQL Server database is defined as: [ID] [int] IDENTITY, as the data grows, the ID value is close to 2147483647 (the value range for int is:-2 147 483 648 to 2 147 483 647). , although the old data has been archived, but the table needs to retain the most recent 100 million data, how to solve the ID value of the rapid explosion problem?

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

Current identity value: The ID value that is used to record and save the last system assignment, the next assignment ID is: the current identity value + identity increment (usually +1, can also be set by itself);

Current column value: The maximum value of this ID value so far;

Second, Reset Process

(i) The following is the test Reset identity column, first use the following SQL to create a test table:

--Create a test tableCREATETABLE[Dbo].[Test_identity]([Identityid][Int]IDENTITY (1,1)NotNull,[Name][NChar](10)Null,CONSTRAINT[Pk_testid]PRIMARYKEYCLUSTERED([Identityid]ascwith (Pad_index Span style= "color: #808080;" >= off, Statistics_norecompute = off, ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = on) on [primary]) on [primary           

(b) The insertion ID value is displayed, the record of the inserted table [test_identity] as shown in Figure1, followed by the implicit insertion of the ID value, as shown in the record of the inserted table [test_identity], as Figure2.

--Show Insert ID valueSETIdentity_insert[Test_identity]OnINSERTInto[Test_identity](Identityid,name)select 1000,  ' name1 set identity_insert [ off--< Span style= "color: #008080;" > Implicit Insert ID value insert into [< Span style= "color: #ff0000;" >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. Executes the following SQL statement, which returns the information represented by: Current identity value ' 1001 ', current column value ' 1001 ', as shown in Figure2.

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

(d) The implicit insertion of the ID value, the record of the inserted table [test_identity], as shown in Figure3. So executing the above SQL statement will not reset the current identity value, you can rest assured that execution.

-- implicit Insert ID value [test_identity](Name)'name3'       

(Figure3: Data logging)

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

(v) DBCC CHECKIDENT (' table_name ') or DBCC CHECKIDENT (' table_name ', reseed) if the current identity value of the table is less than the maximum identity value stored in the column, it is reset with the maximum value in the identity column.

Because the result above is: the current identity value ' 1002 ', the current column value ' 1002 ', so execute the following SQL statement is not affected, when will it affect? Reference: ( when executing the following SQL command in Figure4 state, the result will be as shown in Figure7 )

-- Reset Identity value DBCC checkident ('test_identity', reseed)/* Check for identity information: Current identity value ' 1002 ', current column value ' 1002 '. DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. */

(vi) The current value of DBCC checkident (' table_name ', reseed, New_reseed_value) is set to New_reseed_value. If a row has not been inserted into the table since the table was created, the first row inserted after the DBCC checkident is executed uses new_reseed_value as the identity. Otherwise, the next inserted row will use New_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, a No. 2627-number error message is generated later when the table is referenced.

To understand the above description, you can perform the following tests:

1) Reset the current value to New_reseed_value = 995, and execute the following SQL statement to return the following information as shown below;

-- Reset Identity value DBCC checkident ('test_identity995)/* Check identity information: Current identity value ' 1002 ', current column value ' 995 '. DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. */

2) continue to insert data into the [test_identity] table, execute the following SQL statement after inserting the results as shown in Figure4; the inserted ID value is New_reseed_value + 1 = 996;

-- implicit Insert ID value [test_identity](Name)'name4'       

(Figure4: Data logging)

3) Look at the current identity value, and compare it with the above, you can understand the meaning of the "present identity value" and "Current column value";

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

4) Continue to insert data into the [test_identity] table, execute 3 times after the table data as shown in FIGURE5;

-- implicit Insert ID value [test_identity](Name)'name5'       

(FIGURE5: Data logging)

5) What happens if you continue to insert data into the [test_identity] table now? A No. 2627 error message will be generated, such as the following error message;

Msg 2627, Level 14, State 1, line 2nd

violated the primary KEY constraint ' Pk_testid '. cannot be in object ' dbo. Insert duplicate key in Test_identity '.

Statement has been terminated.

6) below to test the creation of the table after the row is not inserted, if this time to perform reset the identity value what happens? emptying the [test_identity] table and then re-setting the identity value, the returned information is as follows;

-- empty table [test_identity]-- Reset Identity value DBCC checkident ('test_identity995)  /* Check identity information: Current identity value ' NULL ', current column value ' 995 '. DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. */

7) Insert data to the [test_identity] table at this time, as shown in Figure6, which shows: " if you have not inserted the row into the table since the table was created, the first row inserted after the DBCC checkident is executed will use New_reseed_ Value as the identity. "

-- implicit Insert ID value [test_identity](Name)'name5'       

(Figure6: Data logging)

(Figure7: Data logging)

8) If we delete the records of Identityid 1000 and 1001, and then continue inserting the data, will we regenerate the 1000 and 10001 values? The effect is as shown in Figure10 (re-covering);

-- Delete and [test_identitywhere Identityid = +[test_identitywhere Identityid =1001          

(Figure8: Data logging)

-- Reset Identity value DBCC checkident ('test_identity996)-- implicit insertion id value [test_identity  ](Name)'name6'             

(FIGURE9: Data logging)

(Figure10: Data logging)

(vii) Summary: Here, we can already solve the problem of the ID value of the fast explosion, because our old data will be archived regularly, so there is no 2627 error message, and another scenario is when the Figure5 occurs, you can execute DBCC CHECKIDENT (' Test_ Identity ', reseed), set to the current column maximum value as the identity value, preventing the 2627 error message from appearing.

Third, Additional Information

In MySQL, there are also features similar to identity:

' IDs ' int (one) unsigned not NULL auto_increment

When creating a table, there is an option auto_increment=17422061, which can set the starting value directly, and also set the step size:

SHOW VARIABLES like ' auto_inc% ';

Starting value: Auto_increment_offset

Step: Auto_increment_increment

SET @auto_increment_increment = 10;

SELECT last_insert_id ();

Four, Reference Documents

Resetting the value of the Identity identity column for MSSQL

DBCC checkident (Transact-SQL)

@ @IDENTITY in SQL Server, scope_identity and ident_current

SCOPE_IDENTITY (Transact-SQL)

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------

1, newid length, like 4 floor said, each page to store the number will be less, query to access the data page more, popular point, that is I/O will be higher. Performance is not good enough.
2, 3 floor said newid disorderly, not suitable to do the aggregation index, is correct.
3, for the database underlying operations. It is clear that the numeric type becomes 16-binary. There are some unpredictability in the form of characters or other types to the bottom. This requires additional information to determine the data. So it is generally recommended to use the numeric type to the master key and index, add, the fastest operation is 2 binary, in fact, is the SQL Server bit type. However, because the indexes need to consider selectivity, it is not recommended to use bit types for indexing. Selectivity is not high.
4, NEWID claims that 3,000 years will not repeat, but almost no project needs so old. The int type is sufficient to hold 200 million data. More than enough for most projects.
5. Your article only highlights the non-repetition, not the efficiency.
6, because of newid uncertainty, in the match, such as table association, there will be some internal friction.
In summary, it is not recommended to use NEWID to master keys or indexes under very special circumstances.

SQL Server resets the value of the Identity identity column (int exploded) (reproduced)