possible to reuse an identity filed value after deleting row

Question

Is it possible to reuse an identity field value after deleting rows in SQL Server 2008 Express? Here is an example. Suppose I have a table with an Id field as a primary key (identity). If I add five rows, I will have these 5 Ids: 1, 2, 3, 4, 5. If I were to delete 3 number row, and then add two more, the new rows would have Ids: 6, 7 . Is it possible add new row place of deleted row with same id.

Sujeet Suman · Answer

Manish the "DBCC CHECKIDENT" management command is used to reset identity counter. Example: DBCC CHECKIDENT ('[TestTable]', RESEED, 0) GO Please once go through below link. This may help you more: https://technet.microsoft.com/en-us/library/ms176057.aspx https://technet.microsoft.com/en-us/library/ms188059.aspx If this is helpful for you then please accept my Answer.

Priyaranjan K S · Answer

Hi Manish , You can set it manually if you know the identity that has to be reussed using SET IDENTITY_INSERT .Please refer the below code eg : 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 Further reference : https://msdn.microsoft.com/en-IN/library/ms188059.aspx Thanks, Priyan If you found this useful Please mark this as Answer

possible to reuse an identity filed value after deleting row

Answers (2)