The difference between DELETE ,DROP and TRUNCATE is one of the most common interview questions. DELETE,DROP and TRUNCATE SQL queries are often used in SQL Server to delete data from a database.
Delete Command is used to delete data from a table,delete command required permission on a database along with select permission.
Following example Deletes data from a table.
create table Student(Name varchar(20),ID int,Dept varchar(50),address varchar(50))
–Insert values
Insert into Student values(‘Tom’,101,’CS’,’ABC’)
Insert into Student values(‘Kerry’,102,’IT’,’ADC’)
Insert into Student values(‘Sam’,103,’EC’,’ADF’)
Insert into Student values(‘Sohit’,104,’EN’,’ADH’)
Insert into Student values(‘Naman’,105,’ME’,’AWR’)
Insert into Student values(‘Will’,106,’CE’,’DWS’)
- Delete is DML Command.
- Delete is Executing Using row lock, Each row in table is locked for table.
- We can use where clause with delete to filter and delete Specific record.
- It maintain the log So it is slower then Truncate.
- The delete statement removes rows one at a time, and records and entry in transaction log for each deleted row.
- To use Delete you need to at least Delete permission.
- Delete can be used with Index Views.
- Delete uses more transaction space than truncate Statement.
- Delete can be rollback.
Drop is used to remove data and structure of a entity like table ,View,Store proc, Indexes and Constraints.
Following Example drop table from a database .
- The Drop command removes a Table from database.
- All table’s row, indexes and privileges will also be removed.
- No DML trigger will be fired.
- The operation can not be roll back.
- Drop and truncate are DDL command.
Truncate is used to remove all rows from a table.Truncate is faster than delete because truncate does not keep Logs.
- Truncate is DDL command.
- We can not use Where clause in Truncate.
- Minimum logging in Transaction log, So it is performance wise faster.
- Truncate uses the less transaction space than delete statement.
- Truncate can not Used with Index Views.
- Can not be roll back.
- At time of Execution Truncate use table lock to remove all data from a table.
Following Example removes all rows from a table.
You’ve achieved the impossible: rendering learning pleasant and engaging!
This was an outright delight to peruse.
my blog … e-transfer payday loans canada 24/7
Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks. nimabi
I simply couldn’t depart your web site before suggesting that I really loved the
usual info a person supply for your visitors?
Is gonna be back frequently in order to inspect new posts
Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks. nimabi