Como redefinir um novo valor para a coluna identity em T-SQL

Antes de mais nada, não importa qual é o valor da coluna identity. Está sempre bem, desde que seja único. Afinal, as pessoas não precisam de ver o valor.

No entanto, se é realmente necessário redefinir os valores da identidade (por exemplo, para um teste ou para um projecto), isso pode fazer-se no SQL Server.

Suponha-se o seguinte cenário, uma série de registos apagados de uma tabela com uma coluna identity. Como definir manualmente um novo valor de identidade para a coluna de identidade?

A função 

IDENT_CURRENT(<table_or_view>)

retorna o último valor identity gerado para a tabela. O valor da última identidade gerado pode ser para qualquer sessão e para qualquer âmbito.

DBCC CHECKIDENT
(    <table_name>          
[, { NORESEED | { RESEED [, <new_reseed_value> ] } } ])

pode usar-se para definir manualmente um novo valor identity actual para a coluna de identidade.

Como funciona:

CREATE TABLE Product (
ProductID INT IDENTITY,
ProductName VARCHAR(200),
Price INT);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto1', 100);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

SELECT * FROM Product;

DELETE FROM Product WHERE ProductID >= 2;

INSERT INTO Product (ProductName, Price)
VALUES ('Produto1', 100);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

O último valor identity retorna 10.

SELECT IDENT_CURRENT ('dbo.Product') AS Current_Identity;

Agora, aplicando o CHECKIDENT com RESEED

DELETE FROM Product WHERE ProductID >= 2;

DECLARE @MaxProductID AS INT;
SELECT @MaxProductID = MAX(ProductID) FROM dbo.Product;

DBCC CHECKIDENT ('dbo.Product', RESEED, @MaxProductID);

O último valor identity retorna 1.

SELECT IDENT_CURRENT ('dbo.Product') AS Current_Identity;

INSERT INTO Product (ProductName, Price)
VALUES ('Produto2', 250);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto3', 300);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto4', 340);

INSERT INTO Product (ProductName, Price)
VALUES ('Produto5', 500);

SELECT * FROM Product;

A execução pode ser analisada aqui.

Para aplicar a verificação do último valor identity gerado para todas as tabelas de bases de dados, utilizar o script explanado aqui.

Documentação:

IDENT_CURRENT (Transact-SQL)

DBCC CHECKIDENT (Transact-SQL)

Licença CC BY-SA 4.0 Silvia Pinhão Lopes, 16.2.21