What is the difference between Delete and Truncate command?

Everytime I go for an Interview it is one of the questions that has the highest probability of hitting u - "What is the difference between the Delete and the truncate command?"

Well there are lots of differences between the two commands after all if they would have been similar then the essence of the other would have not been there? So, simply just before giving the answer here are the two commands -

Delete Command -

delete from <table name> <optional where clause>

Truncate Command -

truncate table <table name>

So if we have a look at these two basic commands we find the following differences -

1. Delete command can have an optional where clause which can help delete only a subset of the rows present in the table. So, we can have only 5 rows deleted meeting our condition from a table of say around 1000 rows.
2. Truncate command does not have a where clause associated with it and hence can be used to remove all the entries in a table. Delete command in absence of a where clause behaves just like a truncate command with some exception in the internal working. What this difference is - we will come to know a little later in the article.
3. Delete command when used within transaction can be rollbacked wherease the same is not applicable with the truncate command. When a truncate command was initially designed it was not able to rollback the truncation of the table however with newer versions of ASE we can rollback the truncate command.
4. Delete command is a DML operation however the truncate command is a DDL operation.
5. As delete command makes entries in the log for each row deleted from the table hence it also fires the delete trigger. However, as truncate table operation makes entries for only particular data pages deallocation hence the triggers dont fire at the time of truncate command.

Now coming to the internal working of delete and truncate command -

When we issue a delete command then the ASE server makes entries for each deleted row in the transaction log and hence is a logged operation and consequently a very slow operation on large tables. Also as the deletion is a logged operation hence we can rollback this transaction in case we are deleting within begin and end transaction. So, in short delete is a DML operation.

When a truncate command is issued then the ASE server tries to not perform any data manipulation operation on the table and simply deallocates the data pages associated with the table and parallely makes an entry in the transaction log about the deallocation of the data pages and hence fewer log entries. As only the data pages deallocation information is logged for the table hence it is a very fast operation.

Truncate command also does not removes any indexes created on the table along with rules, checks and constraints. Truncating a complete table will also aquire table level lock till the truncate operation is completed so we cannot perform insert, update etc on the table while the table is being truncated. Truncate command can also be used to truncate only a particular table partition rather than truncating the whole table. In previous versions of ASE the truncate command threw an error when used within a transaction, however with newer versions of ASE truncate command can be rollbacked similar to delete operation and hence this difference between delete and truncate command has diluted over the time.