How SQL Server resets the value of the Identity identity column

Source: Internet
Author: User
Tags execution insert reset

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.