Shulou(Shulou.com)06/01 Report--

DBCC CHECKIDENT (Transact-SQL) Checks the current identity value for the specified table in SQL Server 2016 and, if it is needed, changes the identity 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 is needed, of the specified table in the AdventureWorks2012 database.

USE AdventureWorks2012; GO DBCC CHECKIDENT ('Person.AddressType'); GOB. Reporting the current identity value

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

USE AdventureWorks2012; GO DBCC CHECKIDENT ('Person.AddressType', NORESEED); GOC. Forcing the current identity value to a new value

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

USE AdventureWorks2012

GO

DBCC CHECKIDENT ('Person.AddressType', RESEED, 10)

GO

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

SQL Server resets the value of the Identity identity column (INT explodes)

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

I. background

The Id field in Table An in the SQL Server database is defined as: [Id] [int] IDENTITY (1Magne1). With the continuous growth of the data, the ID value is close to 2147483647 (int values range from-2147483 to 2147483647). Although the old data has been archived, this table needs to retain the most recent 100,000,000 data. is there any way to solve the problem that ID values will explode quickly?

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

Current identification value: current identity value, which is used to record and save the ID value assigned by the last system; the next assignment Id is: current identification value + identity increment (usually + 1, which can also be set by yourself)

Current column value: current column value, the maximum Id value so far

II. Reset process

(1) to reset the Identity identity column for the test, first use the following SQL to create the test table:

-- create a test table CREATE TABLE [dbo]. [Test_Identity] ([IdentityId] [int] IDENTITY (1pr 1) NOT NULL, [Name] [nchar] (10) 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]

(2) display the inserted ID value, the record of the inserted table [Test_Identity] is shown in Figure1, then implicitly insert the ID value, and insert the record of the inserted table [Test_Identity] as shown in Figure2.

Display insert ID value SET IDENTITY_INSERT [Test_Identity] ONINSERT INTO [Test_Identity] (IdentityId,Name) SELECT 1000 insert ID value INSERT INTO [Test_Identity] (Name) SELECT 'name2' implicitly

(Figure1: data record)

(Figure2: data record)

(3) 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 identity value it should have. Execute the following SQL statement, and the information returned indicates that the current identity value is' 1001values, and the current column value is' 1001values, as shown in Figure2.

-- query the identification value DBCC CHECKIDENT ('Test_Identity', NORESEED) / * check the identification information: the current identification value' 1001' and the current column value '1001'. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. , /

(4) insert the ID value implicitly, and then insert the record of the table [Test_Identity] as shown in Figure3. Therefore, executing the above SQL statement will not reset the current identity value, so you can safely execute it.

Implicitly insert ID value INSERT INTO [Test_Identity] (Name) SELECT 'name3'

(Figure3: data record)

-- query the identification value DBCC CHECKIDENT ('Test_Identity', NORESEED) / * check the identification information: the current identification value' 1002 'and the current column value' 1002'. DBCC execution completed. 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 identification value stored in the column, it is reset using the maximum value in the identity column.

Because the result returned above is: the current identity value is' 1002 'and the current column value is' 1002', it does not affect the execution of the following SQL statement. When will it affect? Reference: (when the following SQL command is executed in Figure4, the result will be as shown in Figure7)

-- reset identification value DBCC CHECKIDENT ('Test_Identity', RESEED) / * check identification information: current identification value' 1002', current column value '1002'. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. , /

6. The current value of DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) is set to new_reseed_value. If no rows have been inserted into the table since it was created, the first row inserted after the DBCC CHECKIDENT is executed will be identified by new_reseed_value. 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, an error message 2627 will be generated when referencing the table later.

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

1) reset the current value to new_reseed_value = 995. the information returned by executing the following SQL statement is as follows

-- reset the identification value DBCC CHECKIDENT ('Test_Identity', RESEED, 995) / * check the identification information: the current identification value' 1002', the current column value '995'. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. , /

2) continue to insert data into the [Test_Identity] table, and execute the following SQL statement to insert the result as shown in Figure4; the ID value inserted is new_reseed_value + 1 = 996

Implicitly insert ID value INSERT INTO [Test_Identity] (Name) SELECT 'name4'

(Figure4: data record)

3) if you look at the current identification value and compare it with the one above, you can understand the meaning of [current identification value] and [current column value]

-- query the identification value DBCC CHECKIDENT ('Test_Identity', NORESEED) / * check the identification information: the current identification value is' 996 'and the current column value is' 1002'. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. , /

4) continue to insert data into the [Test_Identity] table. After 3 times of execution, the data of the table is shown in Figure5.

Implicitly insert ID value INSERT INTO [Test_Identity] (Name) SELECT 'name5'

(Figure5: data record)

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

Message 2627, level 14, status 1, line 2

Violated the PRIMARY KEY constraint 'PK_testid'. Duplicate keys cannot be inserted in the object 'dbo.Test_Identity'.

Statement terminated.

6) Let's test that there are no rows inserted after the table is created. What happens if you reset the identity value at this time? Clear the [Test_Identity] table, then reset the identity value, and the returned information is as shown below

-- clear table truncate table [Test_Identity]-- reset identification value DBCC CHECKIDENT ('Test_Identity', RESEED, 995) / * check identification information: current identification value' NULL', current column value '995'. DBCC execution completed. If DBCC outputs an error message, contact your system administrator. , /

7) at this time, insert data into the [Test_Identity] table, as shown in Figure6, which indicates: "if no row has been inserted into the table since the table was created, the first row inserted after the DBCC CHECKIDENT will be identified by new_reseed_value."

Implicitly insert ID value INSERT INTO [Test_Identity] (Name) SELECT 'name5'

(Figure6: data record)

(Figure7: data record)

8) if we delete records with IdentityId of 1000 and 1001, and continue to insert data at this time, will the 1000 and 10001 values be regenerated? The effect is as shown by Figure10 (re-overwritten)

-- delete and delete from [Test_Identity] where IdentityId=1000delete from [Test_Identity] where IdentityId=1001

(Figure8: data record)

-- reset identification value DBCC CHECKIDENT ('Test_Identity', RESEED, 996)-- implicitly insert ID value INSERT INTO [Test_Identity] (Name) SELECT' name6'

(Figure9: data record)

(Figure10: data record)

(7) Summary: at this point, we can solve the problem that the ID value is about to explode, because our old data will be archived regularly, so there will be no 2627 error message; in another scenario, when Figure5 appears, you can execute DBCC CHECKIDENT ('Test_Identity', RESEED) and set the maximum value of the current column to the identification value to prevent the 2627 error message.

III. Supplementary explanation

In MySQL, there are features similar to Identity:

`IDs` int (11) unsigned NOT NULL AUTO_INCREMENT

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

SHOW VARIABLES LIKE 'auto_inc%'

Starting value: auto_increment_offset

Step size: auto_increment_increment

SET @ auto_increment_increment=10

SELECT LAST_INSERT_ID ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.