DELETE, TRUNCATE with RESEED Identity in SQL Server

There is miner difference between DELETE, TRUNCATE with RESEED Identity in SQL Server. For demonstration first we create a table with Identity column then perform DELETE and TRUNCATE operation.

-- CREATE TABLE CREATE TABLE STUDENT_INFO ( [ID] INT IDENTITY(1,1) PRIMARY KEY, [ROLL_NO]INT NOT NULL, [NAME] VARCHAR(50) NOT NULL, )   -- INSERT RECORDS INSERT INTO STUDENT_INFO VALUES(101,'HAIDAR') INSERT INTO STUDENT_INFO VALUES(102,'ARUN')   -- DISPLAY TABLE RECORD SELECT * FROM STUDENT_INFO

 

Screen Shot

Effect of DELETE statement

Delete records from table using DELETE command then insert new record and see table records.

-- DELETE RECORDS DELETE FROM STUDENT_INFO   --INSERT SOME NEW RECORD INSERT INTO STUDENT_INFO VALUES(103,'MANOJ') INSERT INTO STUDENT_INFO VALUES(104,'SAURABH')   -- DISPLAY TABLE RECORDS SELECT * FROM STUDENT_INFO

 

Screen Shot

Here we see, When the DELETE statement is executed without WHERE clause it will delete all the rows. However, when a new record is inserted the identity value (ID column) is increased from 2 to 3. It does not reset but keep on increasing.

Effect of TRUNCATE statement

Delete records from table using TRUNCATE command then insert new record and see table records.

-- TRUNCATE RECORDS TRUNCATE TABLE STUDENT_INFO   -- INSERT NEW RECORDS INSERT INTO STUDENT_INFO VALUES(105,'SANDEEP') INSERT INTO STUDENT_INFO VALUES(106,'ROHIT')   -- DISPLAY TABLE RECORDS SELECT * FROM STUDENT_INFO

 

Screen Shot

Now we see, When the TRUNCATE statement is executed it will remove all the rows. However, when a new record is inserted the identity value (ID column) is increased from 1 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.