Question

What is the difference between delete and truncate commands ?

Hint:

Use \text{DELETE} when you want to remove specific rows or need to be able to undo the change. Use \text{TRUNCATE} when you want to quickly empty an entire table and don't need the data back.

Answer 1

Both \text{DELETE} and \text{TRUNCATE} are used to remove records from a database table, but they work differently. \begin{tabular}{|p{0.45\linewidth}|p{0.45\linewidth}|} \hline DELETE & TRUNCATE
\hline Command Type: It is a DML (Data Manipulation Language) command. & Command Type: It is a DDL (Data Definition Language) command.
\hline Filtering: Can remove specific rows using a \text{WHERE} clause. If no \text{WHERE} clause is used, it removes all rows. & Filtering: Removes all rows from a table. It cannot be used with a \text{WHERE} clause.
\hline Performance: Slower, as it removes rows one by one and records an entry in the transaction log for each deleted row. & Performance: Faster, as it deallocates the data pages used to store the table's data and only records the deallocation in the log.
\hline Rollback: The operation can be undone using \text{ROLLBACK} because each deletion is logged. & Rollback: The operation cannot be rolled back in most database systems as it's a minimally logged operation.
\hline Triggers: Will fire any \text{DELETE} triggers associated with the table. & Triggers: Will not fire any \text{DELETE} triggers.
\hline Identity Reset: Does not reset the table's identity column value. & Identity Reset: Resets the identity column back to its seed value.
\hline \end{tabular}