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.
Example
Output
DELETE all record from table
Don't use the WHERE clause to delete all data from table.
Let see below example.
Example
Output
Insert Data after DELETE
Insert new row and select data from table.
Example
Output
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.
Example
Output
Insert Data after TRUNCATE
Insert new row and select data from table.
Example
Output
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
Output
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 |