SQL Server Identity Column

 /*

 if a record is deleted, the IDENTITY column value for that record is also deleted. If a new record is inserted, its value for the IDENTITY column will be incremented from the previous figure in the column. It is not possible to reuse the value that was originally used by the now deleted record

 how do you reuse the value that was assigned to the deleted record?

*/

-- CREATE DB, TABLE AND RECORDS

CREATE DATABASE School

GO

USE School

GO

CREATE TABLE Students

(

   Id INT PRIMARY KEY IDENTITY(2,2), -- SEED 2 AND INCREMENT VALUE 2

   StudentName VARCHAR (50),

   StudentAge INT

)

GO

INSERT INTO Students VALUES ('Sally', 25 )

INSERT INTO Students VALUES ('Edward', 32 )

INSERT INTO Students VALUES ('Jon', 24 )

INSERT INTO Students VALUES ('Scot', 21)

INSERT INTO Students VALUES ('Ben', 33 )

SELECT * FROM Students

DELETE FROM Students WHERE StudentName = 'Jon'

SELECT * FROM Students -- id 6 deleted

--Manually Insert Record with specific ID value which gives error

INSERT INTO Students VALUES (6, 'Nick', 22 )

/*

Msg 8101, Level 16, State 1, Line 26

An explicit value for the identity column in table 'Students' can only be specified when a column list is used and IDENTITY_INSERT is ON.

*/

-- TO INSERT MANUALLY 

SET IDENTITY_INSERT Students ON;

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 ) -- succeeded 

SELECT * FROM Students

--Try Inserting Duplicate Values it will give error 

INSERT INTO Students(Id, StudentName, StudentAge) VALUES (6, 'Nick', 22 )

/*

Msg 2627, Level 14, State 1, Line 35

Violation of PRIMARY KEY constraint 'PK__Students__3214EC071AD8EC3F'. Cannot insert duplicate key in object 'dbo.Students'. The duplicate key value is (6).

The statement has been terminated.

*/

SET IDENTITY_INSERT Students OFF;-- important