Difference Between Truncate, Delete and Drop

Truncate: TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions. TRUNCATE is faster than the DELETE query.

Syntax:

TRUNCATE TABLE TABLE_NAME

1. DDL (Data Definition Langauge)

2. Can not use where clause

3. Fast

4. Drop the table and recreates it.

5. No Triggers

6. Truncate removes all rows from the table.

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.

Syntax:

DELETE FROM Table_Name;

DELETE FROM Customers WHERE OrderId > 1000;  

1. DML (Data Manipulation Language)

2. Where clause can be used

3. Slow

4. Delete the rows one by one

5. Triggers can be used

6. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

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.

Syntax:

DROP TABLE Table_Name

1. The DROP command removes a table from the database.

2. All the tables' rows, indexes, and privileges will also be removed.

3. No DML triggers will be fired.

4. The operation cannot be rolled back.

5. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.

6. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back

---

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Follow Programmers Door for more.

#programming #blog #placement #sql #truncate #delete