T-SQL id value

/*  * ********************************* Identity) A value is a special value. It depends on the ID column and is automatically maintained by SQL Server. It is an auto-increment value and is generally not repeated. however, SQL server does not maintain that the identity value is unique (to ensure that the identity value is unique, the primary key or unique key constraint should be used in this column). Limit 1. the identity value is not continuous, in the following situations, the ID value may be non-consecutive. A, transaction rollback: if the transaction is rolled back when data is inserted, the last identifier will be recorded when the record is inserted, the id value inserted next time will be discontinuous. Rollback transactions include manual transaction rollback and SQL Server automatic transaction rollback. ***********************************  */  Use  Test  Go  If   Object_id ( '  Tempdb .. # ' ) Is   Not   Null      Drop   Table  #  Go  Create   Table  # A (ID  Int   Identity ( 0 , 1  ), B Int   Unique  )  Insert   Into # A (B) Values ( 1  );  Insert   Into # A (B) Values ( 2  );  --  A. Manual transaction rollback  Begin  Tran  Insert   Into # A (B) Values ( 3  );  Rollback   Tran  Insert   Into # A (B) Values ( 3  );  Select   *   From #  /*  Id B 0 1 1 2 3 3  */  --  B. Automatic transaction rollback due to operation failure  Insert   Into # A (B) Values ( 3  );  Insert   Into # A (B) Values ( 4 );  Select   *   From  #  /*  Id B 0 1 1 2 3 3 5 4 the preceding query results do not have an identifier 2 because the transaction is rolled back manually, and no identifier 3 is because inserting data violates the unique constraint, caused by automatic rollback of transactions  */  /*  B. delete a record: no matter whether the id value of the deleted record is newly added or the previously added record is deleted, the record value will not be recycled. Therefore, deleting a record may lead to discontinuous identification values.  */  If   Object_id ( ' Tempdb .. #  ' ) Is   Not   Null      Drop   Table  #  Go  Create   Table  # A (ID  Int   Identity ( 0 , 1 ), B  Int  )  Insert   Into  # A (B)  Select   1   Union   All  Select   2  --  Delete a record  Delete   From # Where B =   2  Insert   Into # A (B) Values ( 2  )  Select   *   From  #  /*  Id B 0 1 2 2 from the results, we can see that the id value 2 is not recycled because the record is deleted, so the newly added record's ID value is still 3  */  Drop  Table  #  /*  C. Use DBCC checkident to reset the id value: You can use the reseed option in DBCC checkident to reset the current ID value of the specified table. Resetting the id value may also cause the id value to be discontinuous.  */  If   Object_id ( '  Tempdb .. #  ' ) Is   Not   Null      Drop   Table #  Go  Create   Table  # A (ID  Int   Identity ( 0 , 1  ), B  Int  )  Insert   Into # A (B) Values (1  )  --  Reset current ID value  DBCC Checkident (# A, reseed, 1  )  Insert # A (B) Values ( 1  )  Select   *   From  # Drop   Table  #  --  DBCC checkident usage: http://technet.microsoft.com/zh-cn/library/ms176057%28zh-tw, SQL .90%29.aspx  DBCC  Checkident (#)  DBCC  Checkident (# A, noreseed)  /*  After the first insertion, the current ID value is 0, and then the current ID is set to 1 through DBCC checkident. When the record is inserted again, the newly inserted id value is 2  */  /* * *********************************** 2, duplicate id value: Because SQL server does not maintain the unique id value, the Unique id value may exist in tables that do not have a primary key or a unique key, duplicate identifiers generally have the following two situations:: force Insert the id value ***********************************  */  If   Object_id ( '  Tempdb .. #  ' ) Is   Not   Null      Drop   Table  #  Go Create   Table  # A (ID  Int   Identity ( 1 , 1  ), B  Int  )  Insert   Into # A (B) Values ( 1  ) --  Insert duplicate ID values  Set   Identity_insert # On   Insert   Into # A (ID, B) Values ( Scope_identity (), 2  )  Set   Identity_insert # Off   Select  *   From  #  Drop   Table  #  /*  The identification values of the preceding two records are the same, because when 2nd records are inserted, the same identification value as 1st records is forcibly specified; then, the identity value is reset using DBCC checkident: if you reset the id value to an id value smaller than the existing ID value in the table (if the id value increment is negative, it is greater than the existing ID value ), the id value of the newly inserted column is repeated.  */  If   Object_id ( '  Tempdb .. # ' ) Is   Not   Null      Drop   Table  #  Go  Create   Table  # A (ID  Int   Identity ( 1 , 1  ), B Int  )  Insert   Into # A (B) Values ( 1  )  --  Reset id value  DBCC Checkident (# A, reseed, 0  )  Insert # A (B) Values ( 2 )  Select   *   From  #  Drop   Table  #  /*  After the record is inserted for 1st times, the current ID value is 1, and then the current ID value is set to 0 through DBCC checkident. Therefore, when the record is inserted again, the newly inserted id value is 1.  */ 

Reference: SQL Server 2005 Development, Management and Application Instances