What is the Difference Between Truncate and Delete in SQL?

The TRUNCATE TABLE statement deletes all rows from a table while preserving its structure and other elements such as columns, constraints, indexes etc. On the other hand, DELETE statement removes certain or all rows from a specified table based on WHERE clause.

Charlotte Wilson16/01/231 minute read0 Comments

73.9k

The TRUNCATE TABLE statement deletes all rows from a table, but the structure of the table and its columns, constraints, indexes, and so on remain intact. To delete the table definition in addition to its data, you can use the DROP TABLE statement. The Delete command is useful for deleting all or certain rows from a table specified by a Where clause. The truncate command deletes all rows from a table.

We can't use a Where clause in this. The SQL Delete command places a lock on each row that requires deleting from a table. The SQL Truncate command places a table and page lock to delete all records. The Delete command is slower than the Truncate command.

You cannot use the truncate command with indexed views. The truncate command only deletes all rows from a table. It does not remove columns, indexes, constraints, and schema. The TRUNCATE TABLE command removes data from a table, but not the table itself.

Truncate removes all rows, but not the table itself, is essentially equivalent to deleting without where clause, but it's usually faster. If you don't know how SQL Server works, you won't know how to handle certain situations where experience is needed. Most sites and even Microsoft say that Truncating will remove the entire schema and all tables. What did I say? And what about DROP? (silence).

The TRUNCATE command is a data-definition language operation. It is used to delete all records in a table. Deletes all records in an existing table, but not the table itself. The structure or schema of the table is preserved.

The TRUNCATE command is like a DELETE command without the WHERE clause with a much smaller safety net. SQL Server avoids truncating a table with foreign keys that reference it, due to the need to check the foreign keys in each row. If I were doing this cumulative report more often, I would probably keep the table and use truncate. You cannot use TRUNCATE TABLE on a table that is referenced by a FOREIGN KEY constraint; instead, use the DELETE statement without a WHERE clause. In PostgreSQL, you need the TRUNCATE privilege; in SQL Server, the minimum permission is the ALTER table; in MySQL, you need the DROP privilege. TRUNCATE TABLE locks the entire table to delete data from a table; therefore, this command also uses less transaction space than DELETE.