MS SQL Server 自增(Identity)主鍵重複

刚处理完毕月结速度问题, 就接到用户发来错误报告,  保存时主鍵重複!

打开程序一看是这是一个自增字段

/*1*/select IDENT_CURRENT('table_xyz');
/*2*/select max(inc_field) from table_xyz t;

 

呵， 搞笑，当前值/*1*/比最大值/*2*/还小。

 

沒有 IDENT_UPDATE 函數可以使用

解决办法

打开 SQL Server Analyzer 执行
dbcc   checkident ('table_xyz', RESEED,  当前最大值)

 

 

 

---

 

SQL Server HELP:

 

DBCC CHECKIDENT

Checks the current identity value for the specified table and, if needed, corrects the identity value.

Syntax

DBCC CHECKIDENT
    ( ' table_name '
         [ , { NORESEED
                | { RESEED [ , new_reseed_value ] }
            }
        ]
    )

Arguments

' table_name '

Is the name of the table for which to check the current identity value. Table names must conform to the rules for identifiers. For more information, see Using Identifiers . The table specified must contain an identity column.

NORESEED

Specifies that the current identity value should not be corrected.

RESEED

Specifies that the current identity value should be corrected.

new_reseed_value

Is the value to use in reseeding the identity column.

Remarks

If necessary, DBCC CHECKIDENT corrects the current identity value for a column. The current identity value is not corrected, however, if the identity column was created with the NOT FOR REPLICATION clause (in either the CREATE TABLE or ALTER TABLE statement).

Invalid identity information can cause error message 2627 when a primary key or unique key constraint exists on the identity column.

The specific corrections made to the current identity value depend on the parameter specifications.

DBCC CHECKIDENT statement	Identity correction(s) made
DBCC CHECKIDENT (' table_name ' , NORESEED)	The current identity value is not reset. DBCC CHECKIDENT returns a report indicating the current identity value and what it should be.
DBCC CHECKIDENT (' table_name ' ) or DBCC CHECKIDENT (' table_name ' , RESEED)	If the current identity value for a table is lower than the maximum identity value stored in the column, it is reset using the maximum value in the identity column.
DBCC CHECKIDENT (' table_name ' , RESEED, new_reseed_value )	The current identity value is set to the new_reseed_value . If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

 

The current identity value can be larger than the maximum value in the table. DBCC CHECKIDENT does not reset the current identity value automatically in this case. To reset the current identity value when it is larger than the maximum value in the column, use either of two methods:

• Execute DBCC CHECKIDENT (' table_name ' , NORESEED) to determine the current maximum value in the column, and then specify that as the new_reseed_value in a DBCC CHECKIDENT (' table_name ' , RESEED, new_reseed_value ) statement.
• Execute DBCC CHECKIDENT (' table_name ' , RESEED, new_reseed_value ) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (' table_name ' , RESEED).

Result Sets

Whether or not any of the options are specified (for a table containing an identity column; this example uses the jobs table of the pubs database), DBCC CHECKIDENT returns this result set (values may vary):

Checking identity information: current identity value '14', current column value '14'.



DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.

Examples

A. Reset the current identity value, if needed

This example resets the current identity value, if needed, of the jobs table.

USE pubs



GO



DBCC CHECKIDENT (jobs)



GO

B. Report the current identity value

This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

USE pubs



GO



DBCC CHECKIDENT (jobs, NORESEED)



GO

C. Force the current identity value to 30

This example forces the current identity value in the jobs table to a value of 30.

USE pubs



GO



DBCC CHECKIDENT (jobs, RESEED, 30)



GO