possible to reuse an identity filed value after deleting row

The following example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

 

USE AdventureWorks2012;

GO

-- Create tool table.

CREATE TABLE dbo.Tool(

ID INT IDENTITY NOT NULL PRIMARY KEY,

Name VARCHAR(40) NOT NULL

);

GO

-- Inserting values into products table.

INSERT INTO dbo.Tool(Name)

VALUES ('Screwdriver')

, ('Hammer')

, ('Saw')

, ('Shovel');

GO

-- Create a gap in the identity values.

DELETE dbo.Tool

WHERE Name = 'Saw';

GO

SELECT *

FROM dbo.Tool;

GO

-- Try to insert an explicit ID value of 3;

-- should return a warning.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');

GO

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT dbo.Tool ON;

GO

-- Try to insert an explicit ID value of 3.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');

GO

SELECT *

FROM dbo.Tool;

GO

-- Drop products table.

DROP TABLE dbo.Tool;

GO