Id booked in key breach transaction

Question

I have the following question:
In an operation of INSERT in a table when a key violation occurs and a ROOLBACK of the transaction, the ID still reserved? Below illustrative examples:

Script of the test table

CREATE TABLE    tb_teste(
    id_teste        INT IDENTITY            CONSTRAINT pk_tb_teste_id_teste      PRIMARY KEY,
    ds_status       VARCHAR(32) NOT NULL    CONSTRAINT uk_tb_teste_ds_status     UNIQUE,
    dt_inclusao     DATETIME    NOT NULL    CONSTRAINT df_tb_teste_dt_inclusao   DEFAULT GETDATE()
)

Script for the INSERT:

BEGIN TRY
    BEGIN TRANSACTION
        SET NOCOUNT ON
        INSERT INTO
            tb_teste
                (ds_status)
        VALUES
                ('teste')
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

Error message in the second INSERT for trying to enter the test value twice:

Violação da restrição UNIQUE KEY 'uk_tb_teste_ds_status'. Não é possível inserir a chave duplicada no objeto 'dbo.tb_teste'. O valor de chave duplicada é (teste).

My questions are:
1. This is a standard SQL SERVER behavior?
2. Is there any way to prevent a failed statement from occupying an id in the table?

Answer 1

Mailson, this is one of several problems when using IDENTITY or SEQUENCE.

Some tables use a substitute key as a primary key, rather than a natural key. Generally this substitute key is a numerical value, sequential and increasing. In some applications there can be no jump in the generation of this sequence. Something that seems simple becomes complicated when considering the occurrence of simultaneous transactions in the same tables and objects. The solution is the creation of own mechanism to generate sequential values for key.

In the article Generation of numerical sequences is the "Sequence Table" method, where an auxiliary table is used to store the last value generated in the sequence. The implementation in stored procedure of obtaining the next value of the sequence simplifies the process. Multiple sequencers can be stored in the same table for different uses.

The use of the stored procedure can be to insert a single row or else to a block of rows. For a single line we can have something like:

-- código #2.15
-- próximo valor disponível
declare @Prox_Valor int;

BEGIN TRANSACTION;

EXECUTE dbo.GetSequence 
     @nome_seq= 'dbo.Pessoa.ID_Pessoa',
     @valor = @Prox_Valor output;

INSERT into dbo.Pessoa (ID_Pessoa, Nome_Pessoa)
  values (@Prox_Valor, 'Paulo Antunes');

COMMIT;

go

The transaction is defined explicitly, being composed of the call to the stored procedure that gets the next key and the inclusion command of the line. In this method the next value generation of the sequence is within the same transaction block of the INSERT command; this is to ensure that there are no holes in the generated sequence.

The details are in the article "Generation of numerical sequences"from which I extracted excerpts for this answer.

---

Creation of the auxiliary table:

-- código #2.12 v2
USE banco; 
go

IF OBJECT_ID ('dbo.tbSequence', 'U') is not null
  DROP TABLE dbo.tbSequence;

CREATE TABLE dbo.tbSequence (
     Seq varchar(30) not null unique,
     Valor int not null default 0
);
go

---

Creation of the stored procedure generating the sequence:

-- código #2.13 v2
IF OBJECT_ID ('dbo.GetSequence') is not null
  DROP PROCEDURE dbo.GetSequence;
go

CREATE PROCEDURE dbo.GetSequence
     @nome_seq varchar(30), 
     @valor int output,
     @n int = 1
as
begin
set nocount on;
declare @retorno int, @NLinhas int;

set @valor= NULL;
UPDATE dbo.tbSequence
  set @valor = Valor = Valor + @n
  where Seq = @nome_seq;

SELECT @retorno= @@error, @NLinhas= @@rowcount;
IF @retorno = 0 
  begin
  IF @NLinhas = 1
    set @valor= @valor - @n +1
  else 
    set @retorno= -1; -- erro na geração do próximo valor
  end;

return @retorno;
end;
go