---
title: "DELETE, TRUNCATE with RESEED Identity in SQL Server"  
description: "DELETE, TRUNCATE with RESEED Identity in SQL Server"  
author: "AVADHESH PATEL"  
published: 2012-09-28  
updated: 2020-07-14  
canonical: https://www.mindstick.com/articles/1018/delete-truncate-with-reseed-identity-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# DELETE, TRUNCATE with RESEED Identity in SQL Server

Here we are going to [explain](https://www.mindstick.com/forum/159699/what-is-a-compilation-error-in-dot-net-core-explain-with-an-example) [difference](https://www.mindstick.com/articles/157114/good-news-or-bad-news-and-the-difference-is) between [DELETE, TRUNCATE](https://www.mindstick.com/blog/313/difference-between-truncate-delete-statement-in-sql-server) with [RESEED Identity](https://www.mindstick.com/forum/460/reseed-identity-column) in [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server). For demonstration first we create a table with [Identity column](https://www.mindstick.com/blog/329/add-identity-column-in-datatable-in-c-sharp) then perform [DELETE and TRUNCATE](https://www.mindstick.com/blog/11279/difference-between-delete-truncate-drop) 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](https://www.mindstick.com/mindstickarticle/84073184-bfed-4208-9dc6-84e956b171e5/images/2a120e2f-2f0b-44c5-bd02-9298a1324daa.png)

##### 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

![DELETE, TRUNCATE with RESEED Identity in SQL Server](https://www.mindstick.com/mindstickarticle/84073184-bfed-4208-9dc6-84e956b171e5/images/636e8bcd-1977-4411-8517-1bb669fb0461.png)

Here you can see, When the DELETE statement is executed without [WHERE clause](https://www.mindstick.com/interview/1909/when-do-you-use-where-clause-and-when-do-you-use-having-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](https://www.mindstick.com/forum/34655/what-is-the-difference-in-drop-delete-and-truncate-command-in-sql-server) 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

![DELETE, TRUNCATE with RESEED Identity in SQL Server](https://www.mindstick.com/mindstickarticle/84073184-bfed-4208-9dc6-84e956b171e5/images/20cca4b9-c1e8-42d5-9117-2589b884e39b.png)

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.

\

---

Original Source: https://www.mindstick.com/articles/1018/delete-truncate-with-reseed-identity-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.