Truncate and Delete both are used to delete data from the table. Both these commands will only delete the data of the specified table; they cannot remove the whole table. But they both differ from each other in many aspects like syntax, performance, resource uses, etc.
Lets take a look at syntax of these commands,
The Syntax for TRUNCATE statement is:
TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name
The Syntax for DELETE statement is:
DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name
Database_name: Is the name of the database in which the table exists. This is optional. If it is not included, the current database context is assumed.
Schema_name: Is the name of the schema in which the table exists. This is optional. If it is not included, the current database context is assumed.
Table_name : Is the name of the table to truncate or from which all rows are removed.
The Differences between Truncate and Delete
Both the statements are similar, but there are many differences that exist between them. Those similarities and differences are explained below:1. Removes the data not the structure:
Both commands only removes rows from a table, but the table structure and its columns, constraints, indexes, and remains same. To remove the table definition in addition to its data, use the DROP TABLE statement.
2. Conditional based deletion of data:
Conditional based deletion of data means we can delete the rows of a table based on some condition specified in WHERE clause as show below,
DELETE FROM authors Where AuthorId IN (1,2,3)
- TRUNCATE - In case of the TRUNCATE command, we can't perform the conditional based deletion because there is no WHERE clause allowed with this command.
- DELETE - The DELETE command provides the functionality of conditional based deletion of data from the table using the WHERE clause.
Most of the articles from internet says: "delete is a logged operation and truncate is not a logged operation", which means when we run the delete command it logs (records) the information about the deleted rows and when we run the truncate command it doesn't log any data. But this is not true; truncate is also a logged operation but in a different way. It uses fewer system and transaction log resources than delete. The TRUNCATE command uses minimum logging resources, which is why it is faster than delete. So both delete and truncate are logged operations, but they work differently as shown below.
- DELETE is a logged operation on a per row basis: The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, in case if you are deleting a huge number of records then it can cause your transaction log to grow. This means the deletion of a huge number of records will use more server resources as it logs each and every row that is deleted. That is why your transaction log will grow very rapidly. Since the delete statement records each deleted row it is also slow. Some people ask what is the use of logging each deleted row??? The answer is when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover your database to the most recent state.
- TRUNCATE logs the deallocation of the data pages in which the data exists: TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows from the table. It won't log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.
Both the TRUNCATE and DELETE commands behave differently against Identity columns. When we use truncate it will reset the counter used by an identity column for new rows to the seed value defined for the column. But in the case of DELETE it will not reset the counter of your identity column. Rather it maintains the same counter for new rows. In both the cases, if no seed was defined the default value 1 is used. As TRUNCATE resets the identity column counter, in the case where you want to retain the identity counter, use DELETE instead of TRUNCATE. T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is to be used.
5. TRUNCATE is a DDL command whereas DELETE is a DML command:
TRUNCATE is a DDL (data definition language) operation and DELETE is a DML (data manipulation language) operation. According to SQL SERVER it's true. But why it is so, why is TRUNCATE is DDL command and DELETE is DML command? Let's look at this;
When we run the TRUNCATE command it puts a "Schema modification (Sch-M)" lock on the table. What is "schema modification (Sch-M)"?
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released. Now you'll ask how it blocks any modification to the table when in the case of TRUNCATE we are performing modifications because we are deleting data? But deleting the data is the one side of coin only. What we see with the internal workings of truncate is (as you read above), that it doesn't remove the data. Rather it actually deallocates the data pages. Because TRUNCATE doesn't perform any data modification in the table that is why the DELETE TRIGGER is not called. I think we are not modifying the data of the table, BUT as you know TRUNCATE resets the Identity counter of the column in the table, which means the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations. Also when you are truncating a table, you can't modify or add any data to the table. So, to become a DDL operation you have to fulfill some of the conditions written below:
- Modifying a table structure or definition comes under DDL operations, and
- When you are modifying the table structure, you can't access the table to do any data modification.
Now we move to the DELETE command. In case of the DELETE command I am not sure which lock is implemented, but as we know and you can read above that DELETE command deletes the rows one by one. It is modifying the data by deleting it from the table, and because DELETE performs data modifications that is why the DELETE TRIGGER is called. The DELETE command does not modify the table structure in any manner, such as like how TRUNCATE modifies the identity column by resetting its value.
To become a DML operation you have to fulfill some of the conditions written below:
- Modifying the table data.
- When you are modifying the table data in the mean time you can't perform any table structure modification on the table.
6. Behavior of Truncate and Delete for Triggers:
Both TRUNCATE and DELETE behave differently for Triggers. As you all know triggers fire whenever any data modification happens in the table. Let's take a look at both one by one:
- TRUNCATE - When we run the TRUNCATE command to delete all the rows of a table it actually doesn't remove any row rather it deallocates the data pages. So in the case of the TRUNCATE command, triggers will not be fired because here no modification takes place. As we know that TRUNCATE is a DDL command, and DDL commands doesn't modify your data; instead they modify your table structure and definition.
- DELETE - In case of DELETE the DELETE trigger will be fired if present and also if the INSTEAD OF and AFTER triggers for DELETE TRIGGER is present then they also will be fired. As we know that delete command is a DML command and it deletes the data on row-by-row basis. So that means delete is modifying the data by deleting it from the table. As we know delete is a DML command and trigger will be fired whenever any DML operation on the table takes place.
There are some restrictions on the use of both of these commands as specified below:
For Delete
- The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
You cannot use TRUNCATE TABLE on tables that:
- Are referenced by a FOREIGN KEY constraint.
- Participate in an indexed view.
- Are published using transactional replication or merge replication.
For using both the statements you need some permissions on the server, which decides whether you can perform the Delete or Truncate action on the table or not. To truncate a table, you need at least ALTER permissions on the table (which is granted by default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles). To delete rows using the delete statement, you only need the DELETE permission.
