SQL Basics-Identify seed overflow

DBCC checkident (Transact-SQL) Checks The current identity value of the specified table in SQL Server and, if it is needed, changes the IdentIT Y value. You can also use DBCC checkident to manually set a new current identity value for the identity column.

Permissions

Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_ Owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Resetting the current identity value, if it is needed

The following example resets the current identity value, if it was needed, of the specified table in the AdventureWorks database.

Use AdventureWorks2012;  GO DBCC checkident (' Person.addresstype '); GO

B. Reporting the current identity value

The following example reports the current identity value of the specified table in the AdventureWorks2012 databas E, and does not correct the identity value if it is incorrect.

Use AdventureWorks2012;   GO DBCC checkident (' Person.addresstype ', noreseed); GO

C. Forcing the current identity value to a new value

The following example forces the current identity value in the AddressTypeID column with the AddressType table to a value of 10. Because The table has existing rows, the next row inserted'll use one as the value, that's, the new current increment VA Lue defined for the column value plus 1.

Use AdventureWorks2012;
GO
DBCC checkident (' Person.addresstype ', reseed, 10);
GO

650) this.width=650; "title=" Capture.png "src=" http://s3.51cto.com/wyfs02/M02/87/DD/wKioL1fjmQvh0yU7AAAxA_ Nnztu035.png-wh_500x0-wm_3-wmp_4-s_1783451932.png "alt=" Wkiol1fjmqvh0yu7aaaxa_nnztu035.png-wh_50 "/>

Https://msdn.microsoft.com/en-IN/library/ms176057.aspx

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

Http://www.cnblogs.com/gaizai/archive/2013/04/23/3038318.html

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:

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--Creating a test table CREATE TABLE [dbo]. [Test_identity]     ([Identityid] [int] IDENTITY () not NULL, [Name] [nchar] (TEN) 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]

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

(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.

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--Display Insert ID value set IDENTITY_INSERT [test_identity] ONINSERT into [test_identity] (identityid,name) SELECT +, ' name1 ' Set Identity_insert [test_identity] off--implicit Insert ID value INSERT INTO [test_identity] (Name) SELECT ' name2 '

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

650) this.width=650; "Width=" 158 "height=" and "title=" F1 "style=" border-width:0px;padding-top:0px;padding-right:0px; Padding-left:0px;margin-right:auto;margin-left:auto;float:none;background-image:none; "alt=" F1 "src=" http:// Images.cnitblog.com/blog/48305/201304/23174424-98d5e90b556a49f1a8030ee0a1921b16.jpg "border=" 0 "/>

(Figure1: Data logging)

650) this.width=650; "width=" 159 "height=" 94 "title=" F2 "style=" border-width:0px;padding-top:0px;padding-right:0px; Padding-left:0px;margin-right:auto;margin-left:auto;float:none;background-image:none; "alt=" F2 "src=" http:// Images.cnitblog.com/blog/48305/201304/23174425-37d6d44a50b647efacee5f3fbe6d3027.jpg "border=" 0 "/>

(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.

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--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. */

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

(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 INSERT INTO [test_identity] (Name) SELECT ' Name3 '

650) this.width=650, "width=" 162 "height=" "title=" F3 style= "border-width:0px;padding-top:0px;padding-right:0px" ;p adding-left:0px;background-image:none; "alt=" F3 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174425-2e869b4175d144a18fdc1cab19fff8d1.jpg "border=" 0 "/>

(Figure3: Data logging)

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--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. */

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

(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 )

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--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. */

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

(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;

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

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

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

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 INSERT INTO [test_identity] (Name) SELECT ' Name4 '

650) this.width=650; "Width=" 164 "height=" "title=" F4 "style=" border-width:0px;padding-top:0px;padding-right:0px ;p adding-left:0px;background-image:none; "alt=" F4 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174426-0092b221679d46f3b4a2275eebe39ee7.jpg "border=" 0 "/>

(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";

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--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. */

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

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 INSERT INTO [test_identity] (Name) SELECT ' Name5 '

650) this.width=650; "Width=" 158 "height=" 189 "title=" F5 "style=" border-width:0px;padding-top:0px;padding-right:0px ;p adding-left:0px;background-image:none; "alt=" F5 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174427-800aad7169a3469ab7ac73ec781f6fe6.jpg "border=" 0 "/>

(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;

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

--Empty tables TRUNCATE TABLE [test_identity]--reset identity value DBCC CHECKIDENT (' test_identity ', reseed, 995)/* 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. */

650) this.width=650; "alt=" Copy Code "src=" Http://common.cnblogs.com/images/copycode.gif "/>

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 INSERT INTO [test_identity] (Name) SELECT ' Name5 '

650) this.width=650; "width=" 165 "height=" "title=" F6 "style=" border-width:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none, "alt=" F6 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174427-579f6c249745476eafc7c175e6d4aad4.jpg "border=" 0 "/>

(Figure6: Data logging)

650) this.width=650; "Width=" 158 "height=" "title=" F7 "style=" border-width:0px;padding-top:0px;padding-right:0px ;p adding-left:0px;background-image:none; "alt=" F7 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174428-01e03a02e3c145a185c56ff6f6097bb7.jpg "border=" 0 "/>

(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 delete from [test_identity] where identityid=1000delete from [test_identity] where identityid=1001

650) this.width=650; "Width=" 164 "height=" "title=" F8 "style=" border-width:0px;padding-top:0px;padding-right:0px ;p adding-left:0px;background-image:none; "alt=" F8 "src=" http://images.cnitblog.com/blog/48305/201304/23174428- Af6b26825c2541539348a2804fb8c4ad.jpg "border=" 0 "/>

(Figure8: Data logging)

--Reset Identity value DBCC CHECKIDENT (' test_identity ', reseed, 996)--Implicit Insert ID value INSERT INTO [test_identity] (Name) SELECT ' Name6 '

650) this.width=650; "Width=" 155 "height=" 169 "title=" F9 "style=" border-width:0px;padding-top:0px;padding-right:0px ;p adding-left:0px;background-image:none; "alt=" F9 "src=" http://images.cnitblog.com/blog/48305/201304/ 23174429-5290aa473c964c8a9ea9a39ee46a3b04.jpg "border=" 0 "/>

(FIGURE9: Data logging)

650) this.width=650; "Width=" 156 "height=" 187 "title=" F10 "style=" border-width:0px;padding-top:0px;padding-right:0 Px;padding-left:0px;background-image:none, "alt=" F10 "src=" http://images.cnitblog.com/blog/48305/201304/23174430 -ffb673f4c97743bd8daae303b14fc349.jpg "border=" 0 "/>

(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 ();

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1855488

SQL Basics-Identify seed overflow