DBCC CHECKIDENT

-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed

( ID INT IDENTITY(1,1) NOT NULL,

DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 

DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.

--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'

--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')

-- After deleting data identity value didn’t reset and the value will be remain same  

---  Output like :  Checking identity information: current identity value '20', current column value '20'.

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed