Here we are going to explain 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
DELETE statement
Now we DELETE the record from table using DELETE command and then insert new record in the table see below.
-- 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 you can 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.
TRUNCATE statement
Now we Delete record from table using TRUNCATE command and then insert new record in table see below.
-- 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 you can 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 started from 1 (which is original value). TRUNCATE resets the identity value to the original seed value of the table.
Leave Comment