Hello All, We are going to start new batch from next week. message/call or mail us for more details.

8 September 2013

DELETE, TRUNCATE with RESEED Identity in SQL Server - DotNet Brother

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.

1 comment: