SET IDENTITY_INSERT SQL Server On Off

Hoje vamos de uma dica rápida e simples do SQL Server.

Quando criamos tabelas e definimos uma determinada coluna como IDENTITY, significa que para cada novo registro inserido, o SQL Server fará o controle de inserção, informando automaticamente o valor para ele, ficando bloqueado informar este número.

A esta situação, dizemos que o atributo IDENTITY_INSERT da tabela, está definido como OFF, ou seja, não será permitida a inserção manual dos valores, e este (como dito) será controlado pelo SQL Server.

Em contra partida a esta situação, podemos definir o atributo para ON, dado-nos assim a permissão, porém, a obrigação de informar este valor, para novos registros inseridos, resumindo, caso o atributo IDENTITY_INSERT seja OFF, não precisamos e não temos permissão de informá-lo, já que ele será controlado pelo SQL Server; caso definido como ON, o SQL Server passa a bola para você, e você, como bom garoto, deverá obrigatoriamente informar este valor para cada registro, ou seja, você é o “dono dos bois”, parabéns!

O comando básico para alterar este atributo é simples.

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

1	SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON \| OFF }

Argumentos

database_name, é o nome do banco de dados no qual a tabela especificada reside.
schema_name, é o nome do esquema ao qual a tabela pertence.
table, é o nome de uma tabela com uma coluna de identidade.

Então vejamos:

Definindo o atributo IDENTITY para ON:

SET IDENTITY_INSERT dbo.cliente ON;

1	SET IDENTITY_INSERT dbo.cliente ON;

Definindo o atributo IDENTITY para OFF:

SET IDENTITY_INSERT dbo.cliente OFF;

1	SET IDENTITY_INSERT dbo.cliente OFF;

Exemplos de inserção.

Como citado, OFF, o SQL Server controla a inserção, ON, nós controlamos. Veja:

CREATE TABLE Cliente(
  Id INT IDENTITY(1,1),
  Nome VARCHAR(100),
  Idade INT
)
 
INSERT INTO Cliente (nome, idade) VALUES ('Claudeci', 30);
INSERT INTO Cliente (nome, idade) VALUES ('João', 40);

CREATE TABLE Cliente(

Id INT IDENTITY(1,1),

Nome VARCHAR(100),

Idade INT

)

INSERT INTO Cliente (nome, idade) VALUES ('Claudeci', 30);

INSERT INTO Cliente (nome, idade) VALUES ('João', 40);

No exemplo acima, o campo Id, será controlado pelo SQL Server. Porém, se nós definirmos o atributo para ON, e tentarmos a mesma instrução INSERT anterior, resultará em um erro. Neste caso, devemos informar o Id do cliente, para cada registro inserido, como mostrado a seguir. Para exemplo, não seguimos uma ordenação, ficando claro que o controle é totalmente do usuário, mas está valendo 🙂

Leave a Reply Cancel reply