We use DELETE and TRUNCATE to remove data from SQL Server table.


Let consider below list total rows is 16 and we set the identity on Id columns.
Identity : We set the Id column as identity means we set that columns auto increment, every time value of Id get columns increment automatically at time of data insertion.

what is diffrence between delete truncate and drop


DELEE specific rows in SQL Server depend on condition

Delete the record where ID between 1 to 10.
Let see below example.

Example

DELETE FROM tblStudent
WHERE Id BETWEEN 1 AND 10

Output

delete data with where clause


DELETE all record from table

Don't use the WHERE clause to delete all data from table.
Let see below example.

Example

DELETE FROM tblStudent

Output

delete all data from sql server table


Insert Data after DELETE

Insert new row and select data from table.

Example

SELECT * FROM tblStudent

Output

insert data in sql table after delete


Note : If you observe the above example you will see that after delete all record from table Id row started from 17 not from 1.
It mean delete not reset the identity of table. It only delete the data from table depending on condition.



Truncate table

Truncate table is also use to remove all record from table.


Let consider below list total rows is 3 and we set the identity on Id columns.

truncate sql table example in sql server

Example

TRUNCATE TABLE tblStudent

Output

truncate sql table example in sql server


Insert Data after TRUNCATE

Insert new row and select data from table.

Example

SELECT * FROM tblStudent

Output

insert data after table truncate


Note : If you observe the above example you will see that after truncate table Id row started from 1 not from 4.
It mean truncate reset the identity of table.



Drop keyword in SQL Server

Drop keyword it will help to drop table, procedure, function, view etc. from the SQL Server.
Drop table drop the structure of table. Let's drop the table.

Example

DROP TABLE tblStudent

Output

drop table examole in sql server using query


Above query drop the table structure, if we try to access same table then it will give below error message.

what is diffrence between delete truncate and drop in sql server

Now below is the answer of most regular interview question that is, What is difference between DELETE, TRUNCATE and DROP.

  DELETE TRUNCATE DROP
Functionality Delete records from table Delete all records from table Delete table structure
Where Clause Can use where condition to delete
data (example given above)
Can't use where condition to delete
data (example given above)
 
Identity Delete not reset table identity (example given above) Truncate reset table identity (example given above)  
Affected Rows Delete show how many
rows get affected
Truncate not show how many
rows get affected
 
Speed Delete is slower Truncate is faster  
Fire Trigger Delete fires trigger Truncate not fire the trigger