0

The query to create the table with its respective field set as IDENTITY is the following:

CREATE TABLE user (
    email varchar(100) primary key
    name varchar(30),
    pwd varchar(10) 
)

Alter table to add IDENTITY field:

ALTER TABLE user ADD id int /*NOT NULL*/ IDENTITY;

The email field to be PRIMARY KEY INDEX will fail if a NULL or DUPLICATED value was set, for example supposed that myemail@domain.com already exists, OK the query fails, but I change the email to anotheremail@domain.com SQL Server generate a new one value for the IDENTITY field based on the query(s) that failed before. My question is why does this happen? (Is this ONLY on SQL Server or other database providers also)

Dale K
  • 25,246
  • 15
  • 42
  • 71
jjoselon
  • 2,641
  • 4
  • 23
  • 37
  • 1
    Possible duplicate of [SQL Identity (autonumber) is Incremented Even with a Transaction Rollback](https://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback) – jontro Jun 13 '19 at 22:41
  • 1
    Personally I stopped concerning myself about the fact that the ID column was not creating a regular sequence and instead decided to consider it to be an 'order of insert' which means I can order the table according to when the rows were inserted. To get a regular integer sequence you can select the table with row_number - https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 – russ Jun 13 '19 at 23:37
  • As @russ says - don't think of an identity column as a counter or a continuous set, think of it as an automatically generated integer key. – TomC Jun 14 '19 at 02:21

1 Answers1

4

Well, this is clearly documented in "CREATE TABLE (Transact-SQL) IDENTITY (Property)":

  • Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Further along the documentation also answers why and suggests what to do if this is not acceptable:

These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations. If you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application.

sticky bit
  • 36,626
  • 12
  • 31
  • 42