TRUNCATE, DELETE, and DROP SQL queries are often used in SQL Server to delete data from a database. Learn tthe difference between truncate and delete in SQL.
TRUNCATE
TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions. TRUNCATE is faster than the DELETE query.
The following example removes all data from the Customers table.
- TRUNCATE TABLE Customers;
- TRUNCATE is a DDL command
- TRUNCATE is executed using a table lock and the whole table is locked to remove all records.
- We cannot use the WHERE clause with TRUNCATE.
- TRUNCATE removes all rows from a table.
- Minimal logging in the transaction log, so it is faster performance-wise.
- TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
- Identity the column is reset to its seed value if the table contains an identity column.
- To use Truncate on a table you need at least ALTER permission on the table.
- Truncate uses less transaction space than the Delete statement.
- Truncate cannot be used with indexed views.
- TRUNCATE is faster than DELETE.
- It does not activate triggers
- can be rolled back when they are within transactions
DELETE
SQL DELETE query deletes all records from a database table. To execute a DELETE query, delete permissions are required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well.
The following query deletes all rows from the Customers table.
- DELETE FROM Customers;
- GO
The following SQL query deletes all rows from the Customers table where OrderID is greater than 1000.
- DELETE FROM Customers WHERE OrderId > 1000;
- GO
- DELETE is a DML command.
- DELETE is executed using a row lock, each row in the table is locked for deletion.
- We can use where clause with DELETE to filter & delete specific records.
- The DELETE command is used to remove rows from a table based on WHERE condition.
- It maintains the log, so it slower than TRUNCATE.
- The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
- Identity of column keep DELETE retains the identity.
- To use Delete you need DELETE permission on the table.
- Delete uses more transaction space than the Truncate statement.
- The delete can be used with indexed views.
- It does activate triggers
- can be rolled back when they are within transactions
DROP
DROP table query removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. DROP command requires to ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.
The following SQL query drops the Customers table and its data and indexes from the current database.
- DROP TABLE Customers ;
- The DROP command removes a table from the database.
- All the tables' rows, indexes, and privileges will also be removed.
- No DML triggers will be fired.
- The operation cannot be rolled back.
- DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
- DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back
Note: Truncate and Delete both are logged operations and both can be rolled back when they are within transactions. It is myth that Truncate is not logged operations. It is indeed logged operations and it logs page level deallocations.
Ref:
