SQL - Truncate and Delete

Truncate and Delete both are used to delete data from the table. Both the command will only delete the data of the specified table without modifying or deleting the structure of the table. Both the SQL statements are used to delete only the data from the table but they both differ from each other in many aspects like syntax, performance, resources uses etc.

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Syntax:

TRUNCATE TABLE
[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

DELETE

Delete command in SQL also removes all rows from a table with logging the individual row deletion in the transaction log. We can use the Where Clause with this (Delete) statement.

Syntax:

DELETE FROM TABLE_NAME
[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

Table_name : Is the name of the table to truncate or from which all rows are removed.
Simple it looks like below query.

DELETE FROM authors

The above command will delete all data from the table author.

In case of delete statements you can limit your delete query using where clause to delete, only particular records that fulfills the condition of where clause will be deleted not the all records.

It looks like below query with where clause.

DELETE FROM authors Where AuthorId IN (1,2,3)