CODEWITHSUNDEEP
sql-server
jjoselon
jjoselon

Reputation: 2811

Why does SQL Server generate a new one identity value even if the insert fails?

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 [email protected] already exists, OK the query fails, but I change the email to [email protected] 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)

Upvotes: 0

Views: 771

Answers (1)

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 4

Related Questions