DBCC CHECKIDENT (Transact-SQL)DBCC CHECKIDENT (Transact-SQL)

適用於:是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2017SQL Server 2017 中檢查指定之資料表目前的識別值,必要的話,會變更識別值。Checks the current identity value for the specified table in SQL Server 2017SQL Server 2017 and, if it is needed, changes the identity value. 您也可以使用 DBCC CHECKIDENT,手動設定識別欄位的新目前識別值。You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


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

引數Arguments

table_nametable_name
這是要檢查目前識別值之資料表的名稱。Is the name of the table for which to check the current identity value. 指定的資料表必須包含識別欄位。The table specified must contain an identity column. 資料表名稱必須符合識別碼的規則。Table names must comply with the rules for identifiers. 兩個或三個部分的名稱必須加以分隔,例如 'Person.AddressType' 或 [Person.AddressType]。Two or three part names must be delimited, such as 'Person.AddressType' or [Person.AddressType].

NORESEEDNORESEED
指定不應變更目前的識別值。Specifies that the current identity value should not be changed.

RESEEDRESEED
指定應變更目前的識別值。Specifies that the current identity value should be changed.

new_reseed_valuenew_reseed_value
這是要當做識別欄位之目前值使用的新值。Is the new value to use as the current value of the identity column.

WITH NO_INFOMSGSWITH NO_INFOMSGS
隱藏所有參考訊息。Suppresses all informational messages.

RemarksRemarks

目前識別值的特定更正會隨著參數規格而不同。The specific corrections made to the current identity value depend on the parameter specifications.

DBCC CHECKIDENT 命令DBCC CHECKIDENT command 進行的識別更正Identity correction or corrections made
DBCC CHECKIDENT ( table_name, NORESEED )DBCC CHECKIDENT ( table_name, NORESEED ) 不重設目前的識別值。Current identity value is not reset. DBCC CHECKIDENT 會傳回識別欄位目前的識別值和最大值。DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. 如果這兩個值不同,則應該重設識別值以防止值序列中發生錯誤或間距。If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.
DBCC CHECKIDENT ( table_name )DBCC CHECKIDENT ( table_name )

中的多個or

DBCC CHECKIDENT ( table_name, RESEED )DBCC CHECKIDENT ( table_name, RESEED )
如果資料表目前的識別值小於識別欄位所儲存的最大識別值,就會利用識別欄位中的最大值來重設它。If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column. 請參閱稍後的<例外>一節。See the 'Exceptions' section that follows.
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) 目前的識別值設為 new_reseed_valueCurrent identity value is set to the new_reseed_value. 如果建立好資料表之後並未插入任何資料列,或者已經使用 TRUNCATE TABLE 陳述式來移除所有資料列,則執行 DBCC CHECKIDENT 之後所插入的第一個資料列就會使用 new_reseed_value 作為識別。If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity.

如果資料列存在於資料表中,下一個資料列會以 new_reseed_value 值插入。If rows are present in the table, the next row is inserted with the new_reseed_value value. 在版本 SQL Server 2008 R2SQL Server 2008 R2 和更舊版中,下一個插入的資料列使用 new_reseed_value 加上目前的遞增值。In version SQL Server 2008 R2SQL Server 2008 R2 and earlier, the next row inserted uses new_reseed_value + the current increment value.

如果資料表不是空的,則將識別值設定為小於識別欄位中最大值的數字,可能會導致下列其中一種狀況:If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

-如果識別欄位有 PRIMARY KEY 或 UNIQUE 條件約束,則之後對資料表進行插入作業時會產生錯誤訊息 2627,因為所產生的識別值會與現有值相衝突。-If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

-如果沒有 PRIMARY KEY 或 UNIQUE 條件約束,則之後進行的插入作業會導致重複的識別值。-If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

例外狀況Exceptions

下表列出 DBCC CHECKIDENT 不會自動重設目前識別值的條件,並提供重設值的方法。The following table lists conditions when DBCC CHECKIDENT does not automatically reset the current identity value and provides methods for resetting the value.

條件Condition 重設方法Reset methods
目前的識別值大於資料表中的最大值。The current identity value is larger than the maximum value in the table. 執行 DBCC CHECKIDENT (table_name, NORESEED) 來判斷資料行中目前的最大值,然後在 DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) 命令中將該值指定為 new_reseed_valueExecute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) command.

-或--OR-

new_reseed_value 設定為非常低的值,並執行 DBCC CHECKIDENT (table_name, RESEED,new_reseed_value),然後執行 DBCC CHECKIDENT (table_name, RESEED) 以更正此值。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) to correct the value.
資料表中的所有資料列都遭到刪除。All rows are deleted from the table. new_reseed_value 設定為所需的起始值,並執行 DBCC CHECKIDENT (table_name, RESEED,new_reseed_value)。Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to the desired starting value.

變更初始值Changing the Seed Value

初始值就是針對載入資料表中的第一個資料列插入識別欄位的值。The seed value is the value inserted into an identity column for the very first row loaded into the table. 所有後續資料列都會包含目前的識別值加上遞增值,其中目前的識別值就是針對資料表或檢視表所產生的最後一個識別值。All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

您無法使用 DBCC CHECKIDENT 來執行下列工作:You cannot use DBCC CHECKIDENT to perform the following tasks:

  • 變更建立資料表或檢視表時針對識別欄位所指定的原始初始值。Change the original seed value that was specified for an identity column when the table or view was created.

  • 重設資料表或檢視表中現有的資料列。Reseed existing rows in a table or view.

    若要變更原始的初始值並重設任何現有的資料列,您必須卸除識別欄位並指定新的初始值來重建此識別欄位。To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. 當資料表包含資料時,識別數字就會加入至含有指定初始值和遞增值的現有資料列。When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. 但是,無法保證資料列的更新順序。The order in which the rows are updated is not guaranteed.

結果集Result Sets

不論是否針對包含識別欄位的資料表指定任何選項,DBCC CHECKIDENT 都會針對所有作業傳回下列訊息 (只指定新的初始值時例外)。Whether or not any of the options are specified for a table that contains an identity column, DBCC CHECKIDENT returns the following message for all operations except when specifying a new seed value.

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

當使用 DBCC CHECKIDENT 來透過 RESEED new_reseed_value 指定新的初始值時,將會傳回下列訊息。When DBCC CHECKIDENT is used to specify a new seed value by using RESEED new_reseed_value, the following message is returned.

Checking identity information: current identity value '\<current identity value>'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

[權限]Permissions

呼叫者必須擁有包含資料表的結構描述,或者必須是 sysadmin 固定伺服器角色、db_owner 固定資料庫角色,或 db_ddladmin 固定資料庫角色的成員。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.A. 必要的話,重設目前的識別值Resetting the current identity value, if it is needed

在必要時,下列範例會重設 AdventureWorks2012AdventureWorks2012 資料庫中指定之資料表目前的識別值。The following example resets the current identity value, if it is needed, of the specified table in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType');  
GO  

B.B. 報告目前的識別值Reporting the current identity value

下列範例會報告 AdventureWorks2012AdventureWorks2012 資料庫中指定之資料表目前的識別值,如果識別值不正確,並不會更正它。The following example reports the current identity value in the specified table in the AdventureWorks2012AdventureWorks2012 database, and does not correct the identity value if it is incorrect.

USE AdventureWorks2012;   
GO  
DBCC CHECKIDENT ('Person.AddressType', NORESEED);   
GO  

C.C. 將目前識別值強制設為新的值Forcing the current identity value to a new value

下列範例會強制將 AddressTypeID 資料表中 AddressType 資料行內的目前識別值設定為 10。The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. 因為資料表目前有資料列,所以下一個插入的資料列將會使用 11 的值,也就是針對資料行值定義的目前增量值加 1。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  

D.D. 在空白資料表上重設識別值Resetting the identity value on an empty table

下列範例會在刪除資料表中所有記錄後,強制將 ErrorLog 資料表中 ErrorLogID 資料行內的目前識別值設定為 1。The following example forces the current identity value in the ErrorLogID column in the ErrorLog table to a value of 1, after deleting all records from table. 因為資料表沒有現有的資料列,所以下一個插入的資料列將會使用 1 作為值,也就是目前的識別值,而不新增針對資料行定義的遞增值。Because the table has no existing rows, the next row inserted will use 1 as the value, that is, the new current identity value, without adding the increment value defined for the column.

USE AdventureWorks2012;  
GO  
TRUNCATE TABLE dbo.ErrorLog
GO
DBCC CHECKIDENT ('dbo.ErrorLog', RESEED, 1);  
GO  

另請參閱See Also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
IDENTITY (屬性) (Transact-SQL)IDENTITY (Property) (Transact-SQL)
複寫識別資料行Replicate Identity Columns
USE (Transact-SQL)USE (Transact-SQL)
IDENT_SEED (Transact-SQL)IDENT_SEED (Transact-SQL)
IDENT_INCR (Transact-SQL)IDENT_INCR (Transact-SQL)