SQL Server - DELETE, TRUNCATE and DROP

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.

DELEE specific rows in SQL Server depend on condition

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

DELETE all record from table

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

Insert Data after DELETE

Insert new row and select data from table.

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.

Insert Data after TRUNCATE

Insert new row and select data from table.

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.

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

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