SQL Server database table primary key Reset

Generally, when designing the table structure, we set the primary key to auto-increment for the convenience of maintenance (this can be simply set in the sqlserver database, in Oracle, it must be implemented through sequences and triggers). During Development, some data is often used for testing. In this way, the auto-increment of the primary key makes the sequence of the primary key not start from 1 when we clear the data in the table. Although it does not affect the usage, it is always uncomfortable, we can use DBCC checkident to reset the start value of the identity column in use. The following describes how to use it:

For example, if you want to delete all records in a table, you can run DBCC checkident (tablename, reseed, 0) in the query analyzer when the ID column starts from 1 );

Check the current Identifier value of the specified table. If necessary, correct the Identifier value.

Syntax

DBCC checkident
('Table_name'
[,{Noreseed
| {Reseed [, New_reseed_value]}
}
]
)

Parameters

'Table_name'

Is the name of the table whose current identity value is to be checked. The table name must comply with the identifier rules. For more information, see use identifiers. The specified table must contain an ID column.

Noreseed

Specifies that the current ID value should not be corrected.

Reseed

Specifies that the current ID value should be corrected.

New_reseed_value

Is the value to be used when the value is re-assigned in the ID column.

Note

If necessary, DBCC checkident will correct the current ID value of the column. However, if the ID column is created using the not for replication clause (in the create table or alter table statement), the current ID value is not corrected.

If the primary key or unique key constraint exists in the ID column, invalid ID information may cause error 2627.

The specific correction to the current Identifier value depends on the parameter specification.

DBCC checkident statement	Id correction
DBCC checkident ('Table_name', Noreseed)	The current ID value is not reset. DBCC checkident returns a report that specifies the current ID value and the expected id value.
DBCC checkident ('Table_name') Or DBCC checkident ('Table_name', Reseed)	If the current ID value of the table is smaller than the maximum id value stored in the column, reset it with the maximum value in the column.
DBCC checkident ('Table_name', Reseed, New_reseed_value)	The current value is setNew_reseed_value. If the row is not inserted into the table after the table is created, the first row inserted after DBCC checkident is executed will use New_reseed_valueAs the identifier. Otherwise, the next inserted row usesNew_reseed_value+ 1. If New_reseed_valueThe value of is less than the maximum value in the identification column. When you reference the table later, error 2627 is generated.

The current ID value can be greater than the maximum value in the table. In this case, DBCC checkident does not automatically reset the current ID value. To reset the current ID value when the current ID value is greater than the maximum value in the column, use either of the following methods:

• Run DBCC checkident ('Table_name', Noreseed) to determine the current maximum value in the column, and then use DBCC checkident ('Table_name', Reseed,
  New_reseed_value) Statement to specify this valueNew_reseed_value.

• SetNew_reseed_valueTo execute DBCC checkident ('Table_name', Reseed,
  New_reseed_value), And then run DBCC checkident ('Table_name', Reseed ).

Result set

Whether or not any options are specified (for tables that contain the ID column; the following example usesPubsDatabaseJobsTable), DBCC checkident returns the following result set (the value may change ):

 
Checking identity information: Current identity value '14', current column value '14'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 

Permission

DBCC checkident permission is granted to the table owner by default,SysAdminFixed server roles andDb_ownerMembers of a fixed database role cannot be transferred.

Example A. Reset the current ID value if necessary

The following example is reset if necessary.JobsThe current Identifier value of the table.

 
Use pubsgodbcc checkident (jobs) Go
 

B. report the current ID value

Example reportJobsThe current ID value in the table. If the id value is incorrect, it is not corrected.

 
Use pubsgodbcc checkident (jobs, noreseed) Go
 

C. Force the current ID value to 30

Force the following exampleJobsThe current ID value in the table is 30.

 
Use pubsgodbcc checkident (jobs, reseed, 30) Go
 

 

Original: http://blog.csdn.net/jxqvip/article/details/6611040