CODEWITHSUNDEEP
sql-serveridentity
Shi Zhang
Shi Zhang

Reputation: 163

SQL Server: Reset Identity If Error Occurred

I have the following table:

create table testTable
(
    id int identity primary key,
    string varchar(256),
    constraint testConstraint unique(string)
)

with the following trigger:

create trigger testTrigger 
on testTable
for delete as
begin
    declare @max_ int
    select @max_= max(id) from testTable

    if @max_ is null   
        set @max_ = 0
        DBCC CHECKIDENT ('testTable', RESEED, @max_)
end

Executing the following commands:

insert into testTable(string) values ('test') --identity = 1
insert into testTable(string) values ('test') --error thrown because duplicate key but **identity = 2**
insert into testTable(string) values ('test1') --due to erroneous identity increment identity = 3

The first insertion sets identity = 1, the second throws an error because of unique(string) constraint but identity is erroneous set to 2.

The question is how do I make it so that errors do not increment identity?

Is there a do-all be-all feature of SQL Server where the identity is ensured to be produced in a sequential fashion based on what's already in the column/table? Thus all edge cases such as this will be captured.

Thanks in advance (:

Upvotes: 1

Views: 69

Answers (2)

Patrick
Patrick

Reputation: 5846

As others have mentioned, don't worry about gaps in the identity column coming from DELETEs.

If you absolutely need an increment number with no gaps in it, you could handle that on the SELECT side when needed.

For example, see ROW_NUMBER ... this can be used along with sorting on a timestamp column of your linking (something like SYSUTCDATETIME) to get the exact sequence of addition for rows, ORDER DESC, with ROW_NUMBER() in the query.

This will NOT be tied to the data, because if rows are DELETEd, these ROW_NUMBERS will change.

Upvotes: 1

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

There shouldn't be any need to prevent gaps in an identity column. It sounds like you might be applying business logic to a surrogate key, which kind of defeats the purpose. You could do a sequence as Sean Lange points out in the comments.

Upvotes: 0

Related Questions