Online Tutorials & Training Materials | STechies.com
Register Login

Difference between DELETE and TRUNCATE in SQL Server

11 Jan 2019 11:51 am || 0

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 deleting and truncate is that the safety net is tighter in case of the delete command. Read on to know more.

Truncate vs Delete

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 TRUNCATE command, the entire table is locked for the removal of all given records. A table lock is initiated for 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; instead, 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 an 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 the use of command To use the truncate command on a table, at least the inclusion of the ALTER permission to the table becomes a necessity. 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 A 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.

TRUNCATE command in SQL

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

Syntax:

TRUNCATE TABLE table_name;

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 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 essential truncate and deletes 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 essential 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 removed.

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.

Features of TRUNCATE command in SQL

Here are some significant 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 defined initially 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 essential difference between delete and truncates 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 to store 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 essential 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 closed for use for all concurrent users if any. Thus, be careful while using this command as it will make the queried table entirely inaccessible for other shared users along with yourself.

Delete command in SQL

SQL Delete statement comes in handy for deleting rows from any given table in a database.

Syntax:

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 delete will lead to the DELETE action being performed only on the chosen set of data. In a nutshell, it is imperative 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.

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.

DROP Command in SQL

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 vital to use the DROP statement carefully.

Syntax:

DROP TABLE table_name;

Example of Delete, Truncate and Drop Command in SQL

/*Create table*/

CREATE TABLE `university`.`us_student_information` ( `si_Id` INT(6) NOT NULL AUTO_INCREMENT , `si_FirstName` VARCHAR(20) NOT NULL , `si_LastName` VARCHAR(20) NOT NULL , `si_email` VARCHAR(50) NOT NULL , `si_contactNumber` VARCHAR(15) NOT NULL , `si_parentContact` VARCHAR(15) NOT NULL , `si_Address` VARCHAR(100) NOT NULL , `si_Class` VARCHAR(6) NOT NULL , `si_Section` VARCHAR(6) NOT NULL , PRIMARY KEY (`si_Id`), UNIQUE (`si_email`), UNIQUE (`si_contactNumber`)) ENGINE = InnoDB;


---------

/*Insert Data into table*/

INSERT INTO `us_student_information` (`si_Id`, `si_FirstName`, `si_LastName`, `si_email`, `si_contactNumber`, `si_parentContact`, `si_Address`, `si_Class`, `si_Section`) VALUES (NULL, 'Suresh', 'Sharma', 'suresh@abc.com', '8077XXXX09', '7065XXXX09', 'Gurugram', '12', 'A'), (NULL, 'Vivek', 'Nautiyal', 'nautiyal.vivek@xyz.com', '9065XXXX11', '8867XXXX00', 'Delhi', '12', 'A')

------------

/* Truncate Table */

TRUNCATE TABLE `us_student_information`;

------------

/* DELETE Data from table */

DELETE FROM `us_student_information` WHERE `si_Id` = 1;

------------

/* Drop Table */

DROP TABLE `us_student_information`;

Difference between drop and truncate

  • 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 on 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 additional 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 those as mentioned above 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 particular 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.