What is the difference between Delete and Truncate in SQL
| TRUNCATE | DELETE |
| TRUNCATE is a DDL command | DELETE is a DML command |
| TRUNCATE is executed using a table lock and the whole table is locked for remove all records. | DELETE is executed using a row lock each row in the table is locked for deletion. |
| We cannot use Where clause with TRUNCATE. | We can use where clause DELETE to filter & delete specific records. |
| TRUNCATE removes all rows from a table. | The DELETE command is used to remove rows from a table based on WHERE condition. |
| Minimal logging in transaction log so it is performance wise faster. | It maintains the log so it slower than TRUNCATE. |
| 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. | The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. |
| Identify column is reset to its seed value if the table contains any identity column. | Identity of column keep DELETE retain the identity. |
| To use Truncate on a table you need at least ALTER permission on the table. | To use Delete you need DELETE permission on the table. |
| Truncate uses the less transaction space than Delete statement. | Delete uses the more transaction space than Truncate statement. |
| Truncate cannot be used with indexed views. | Delete can be used with indexed views. |
| Rollback is not possible. | Rollback is possible. |
| Truncate recovers space. | Delete does not recover space. |