Online Tutorials & Training Materials | STechies.com
Register Login

Difference between DELETE and TRUNCATE in SQL Server

|| 0

Difference between DELETE and TRUNCATE in SQL Server
Stechies

Before we delve into the difference between Delete and Truncate in SQL Server, it is important to understand the definition and function of delete and truncate commands. In this article we will take you through the important differences related to delete vs truncate SQL commands, meanings of delete and truncate in sql, features of delete and truncate in sql server, etc. Even though both commands are known to give the same results, there is a lot of difference between truncate and delete in sql server that have to be figured out by DBAs and developers for their proper usage.

The main difference between truncate and delete is that even though the TRUNCATE command behaves like the DELETE command, it does not include the WHERE clause. Also, another diff between delete and truncate is that the safety net is tighter in case of the delete command. Read on to know more.

What is TRUNCATE Command

The SQL TRUNCATE statement is useful for deleting all rows present in a table with the intent of freeing up table space. The syntax related to TRUNCATE is

TRUNCATE TABLE table_name;

To cite an example, if you want to remove all the rows contained in the table named city, then the truncate command would appear like:

TRUNCATE TABLE city;

If you are wondering when to use the TRUNCATE command, then do know that it is best applied in case you desire to remove all rows in a table really quickly. This is because the usage of the Truncate statement in SQL leads to very less information being logged in the system. This is an important truncate and delete difference as in contrast to the other related commands, the TRUNCATE statement executes faster. A drawback is that when the deletion takes place, every row that is deleted is not logged. This effectively means that it is very important to be careful while giving the truncate command in sql as all the rows in the entire table will be deleted quite fast and the information may not be logged in about every row that is deleted.

The good news is that it is possible to rollback a TRUNCATE statement in SQL Server, which is not a possibility in Oracle. So, it is important not to execute the TRUNCATE statement by mistake as a lot of valuable data may get lost in the process. The data recovery process is long and cumbersome, requires advanced coding and may lead to irrecoverable data loss.

Given the features of TRUNCATE command in SQL, here are some important reasons to go for the command.

  • In case a table has to be ‘reset’ to its original, empty state, then it makes sense to use the TRUNCATE vs the DELETE command. By giving this command, all the rows present in the table will be removed. Further, all the identity key values will be reset to their originally defined values.
  • In case you are in a rush and wish to clear out all the data contained in the various rows of any given table in the database you are working on, then it is wise to use the truncate vs. the delete command. This is an important difference between delete and truncate commands, especially when you need to keep importing test data repeatedly and desire quick actions. The Truncate command also comes in handy when your work demands the regular creation of scratch tables or work tables for the purpose of soring information in SQL Server. Once you are done with these tables, it is easy to use the truncate command to free tablespace.
  • The truncate command in sql is also useful for removing rows from specific tables without the requirement of activating the after delete trigger in a table.

It is important to know that using the truncate command in SQL locks the table on which it is used. This in turn means that the table will be locked for use for all concurrent users, if any. Thus, be careful while using this command as it will make the queried table completely inaccessible to other shared users along with yourself.

What is DELETE Command

The SQL Delete statement comes in handy for deleting rows from any given table in a database. The syntax related to DELETE is

DELETE FROM table_name [WHERE condition];

Even though it is obligatory to provide the table_name, the where clause is optional. In case the WHERE condition is not given then all the rows contained in the specific table will get deleted. However, the presence of the WHERE clause with conditions stated as the rows and columns that have to deleted, will lead to the DELETE action being performed only on the chosen set of data. In a nutshell, it is very important to give the DELETE command carefully as it may lead to the complete deletion of all data contained in a table in case the WHERE clause is missing.

Say, you desire to delete a particular row in the table named “Student” that contains Student_ID as 54, the DELETE command will be given as

DELETE FROM Student WHERE Student_ID = 54;

In case you desire to remove all the rows from the table named Student, the DELETE command will be provided as:

DELETE FROM Student;

If you are wondering about the delete and truncate difference regarding when to use this command, then do know that the DELETE command can be effectively used for removing the records from any given database - either all records or designated rows - as per the usage of the WHERE clause. While executing the DELETE command, it is commonplace for the DBMS to create a log of all removed rows. This kind of logging makes it easier for users to recover from a mistake of giving the DELETE command wrongfully; this is not so easy in case of the TRUNCATE command. In most cases, developers and DBAs find it easier to use the DELETE command rather than the TRUNCATE command as they can recover the lost rows in case of any errors.

What is DROP Command

The SQL DROP command removes any given object from the database. In case you choose to drop a given table from your database, then the DROP command will remove the complete table structure after deleting each of its rows. A table once dropped in this manner cannot be retrieved. Also, as no reference to the dropped table remains valid after this command has been fired, it is very important to use the DROP statement carefully. The syntax for dropping a SQL table structure is as follows:

DROP TABLE table_name;

Say, you want to drop the table named Student, the syntax for this command will be:

DROP TABLE Student;

Delete vs Truncate vs Drop in SQL Server

The difference between truncate and delete in mysql is an important and oft-asked interview question. Given below is a comparison chart depicting the main points of difference between delete and truncate in sql. We will also explain the difference between drop, truncate and delete in the para that follows:

Comparison Chart

Point of Difference

TRUNCATE

DELETE

Type of command

It is a DDL (data definition language)  command

It is a DML (data manipulation language) command.

Execution

While using the TRUCATE command the entire table is locked for the removal of all given records. A table lock is initiated for the purpose of execution.

DELETE is executed with the help of a row lock. When the command is triggered, each row present in the tablespace is locked for deletion.

Usage of WHERE clause

The WHERE clause cannot be used with the truncate clause.

The usage of WHERE clause comes in handy for the sake of selection and filtering of the rows to be deleted. Only the rows and columns specified in the WHERE condition will be deleted.

Removal of rows

The use of truncate command will delete all the rows in a table.

The DELETE statement is responsible for removing only one row at a time rather than removing all the rows contained in the table at a single go. Every time a row is removed, its entry is recorded in the transaction log.

Performance

As there is minimal logging in context to the truncate command, the performance is faster than the delete command.

As a log is created and maintained for each row that’s deleted, the performance of the DELETE command is slower than that of the TRUNCATE command.

Deallocations in the transaction log

The TRUNCATE TABLE command helps in removing all the data; it does so by deallocating the data pages that are used for storing the table data. Once done, only the page deallocations are recorded in the transaction log. Triggers are not fired off as there are no modifications taking place. TRUNCATE is a DDL command so it doesn't modify data; rather it changes the table structure and its definition.

In case of the DELETE command, the DELETE trigger (if present) is fired along with the INSTEAD OF as well as the AFTER triggers for the DELETE TRIGGER (if present). Delete command being a DML command deletes the data on a row-by-row basis and the triggers are fired on the table on which the operation takes place.

Identity column

In case the table possesses any identity column then it will be reset to show its seed value after the truncate command has been used.

When a record is deleted, the identity column value is not reset. In other words, the value of the identity column is retained even after the DELETE command has been used.

Permission for use of command

In order to use the truncate command on a table, at least the inclusion of the ALTER permission with respect to the table becomes a necessity.

In order to use the DELETE command, the DELETE permission has to be applied on the table.

Usage of transaction space

The truncate command uses lesser transaction space in comparison to the delete statement.

More transaction space is needed as each deletion is recorded.

Usage with indexed views

The truncate command is not capable of being used with indexed views.

Delete command can be used along with indexed views.

Foreign key constraints

TRUNCATE command cannot be executed in case the foreign key command constraints apply to the object that has to be deleted.

The DELETE command can be used on the records that contain foreign key constraints.

Along with the above difference between delete and truncate in sql server, it is important to note the differences between TRUNCATE, DELETE and DROP commands. As a complete comparison chart pertaining to the difference between drop delete and truncate is beyond the scope of this article, we have enumerated the difference between delete and truncate when it comes to comparison with the drop command in SQL.

The DROP command is different from sql truncate vs delete in the following ways:

  • The DROP command works quickly and removes the entire table from a given database.
  • When the DROP table command is fired, all the rows, indexes, columns and privileges are also removed.
  • There are no DML triggers fired when the DROP command is given.
  • The DROP and TRUNCATE command operations are not capable of being rolled back once given; conversely, the DELETE command can be rolled back with the help of advanced coding.
  • While DELETE is a DML SQL command query, both DROP and TRUNCATE are DDL commands pertaining to SQL Server.

Once a table has been dropped, all relationships with other objects and tables will be automatically dropped and will become invalid. Along with the integrity constraints, access privileges and other references will also get dropped. In case the table has to be used again then all the integrity constraints, relationships with other objects, access privileges, etc. have to be recreated and established all over again. On the other hand, if a table has been truncated, its structure will remain as in the initial phase. None of the above-mentioned problems will exist.

Conclusion

Now that you are informed about the main differences between truncate, delete and drop commands in sql query, do remember that TRUNCATE and DROP are DDL commands; whereas the DELETE command is a DML command. This point of difference between delete vs truncate vs drop will help you assess the usage and further implications of these commands. So, whereas you can use the DELETE command for removing one or multiple rows from a table, in special situations, you may need to reset a table by using the TRUNCATE command. In case you have any other question that has been left unanswered despite our attempt to give a comprehensive comparison of delete, drop and truncate commands in sql, do write to us. We will be glad to receive your inputs on the difference between truncate and delete commands as well.