Home > DeveloperSection > Articles > DELETE, TRUNCATE with RESEED Identity in SQL Server

DELETE, TRUNCATE with RESEED Identity in SQL Server


Database Database 
Ratings:
0 Comment(s)
 9751  View(s)
Rate this:

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

DELETE, TRUNCATE with RESEED Identity in SQL Server

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

DELETE, TRUNCATE with RESEED Identity in SQL Server

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

DELETE, TRUNCATE with RESEED Identity in SQL Server

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.


Don't want to miss updates? Please click the below button!

Follow MindStick