Thursday, October 16, 2014

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