SQL is one of the most used query languages and has many commands that are used for table processing. As a database consists of tables and to process the database you may need to alter the tables. These alteration commands are like addition, deletion, update, or other table row related operations. These commands are used to delete the data before processing. If the foreign key constraint is being used in the table then it may be difficult to delete table data due to table dependencies on other tables.
SQL is being used by most of the DBAs to delete the table and records or sometimes table structures only. In all, there are three commands that are frequently used by the programmers to perform the delete operation. These commands are most of the times used for individual table or for referenced tables. In case if there is any foreign key used in the table that is another table is linked with that table then it is known as a referenced table. We are going to describe these operations and the properties of all these commands that can help you in performing the operation conveniently.
Basically, you may find two commands in SQL for table data deletion one is DELETE and other is TRUNCATE. These two keywords or table data deletion commands are used to delete table data and both of these commands has its own advantages and disadvantages which should be considered while deciding which command should be used in which condition. Here, today we have brought this post for you, to explain and introduce these commands and the situations in which they are appropriate? You can use the commands as per the situation and requirements.
Before we dive deep into the topic, let us have a quick look at the differences –
Here, we have discussed the major differences only. For a detailed guide, you must go through the blog and check out the comparison of two commands in detail below. Let us discuss first what is SQL Delete Command and what is SQL Truncate command with examples.
Delete is a DML or data manipulation command and is executed using a row lock, in which each row is locked before delete operation command. In fact, to filter and delete any specific record we can use DELETE command along with where clause. Table rows can also be deleted through DELETE command as per WHERE condition that is specified in the clause. For the command, a log has maintained that slow down the command execution time.
In this operation, each table row is deleted one by one and the transaction log maintains each entry of the deleted row. You may need permission to perform delete operation on the table. More transaction space or storage may be required to perform the Delete operation. Even along with indexed views, you can use Delete operation. Table identity is not deleted instead it is still retained along when the delete operation is performed on the table.
As the command only delete rows from the table and it also maintains log sequence number and transaction log, so the transactions that are performed through this operation can be rolled back. You can minimize the number of rows on which the operation will be performed by specifying WHERE clause condition. Moreover, if a large file is deleted through this command then the table may hang on to the empty pages that may require manual release that is done by DBCC SHRINKDATABASE (db_name) command. The commands are executed in the following way:
SQL also has a TRUNCATE command and it uses it as a statement. It deletes the table records by de-allocating the pages of data. In this way, the overhead of resource allocation is reduced while the operation is performed on data and the number of locks can also be controlled and limited as per requirement. In this operation, the transaction log is bypassed and here only one operation that is deallocation of pages is recorded.
Here, in this command, the deleted records cannot be restored as the log is not maintained while performing this operation. Due to the absence of Where clause either all or nothing is removed from the table. The main advantage of this command is that it can specify the table identity back to SEED and deallocated pages are being returned back to the system areas.
The tables involved in replication or log shipping cannot use TRUNCATE command, as to keep the databases consistent you will have to be dependent on the transaction log. For the tables that use foreign key constraint, you cannot use TRUNCATE command as they cannot fire the triggers as well. So, it may also result in inconsistent data as transaction log is not maintained in this command.
In case, if there is a foreign key in the table then, in that case, TRUNCATE command is not used for the tables. If you will use TRUNCATE command with a table that uses the foreign key then an error will be returned.
Truncate is basically a DDL command and is executed along with table lock in which the whole table is locked before removing the table records. The user cannot use WHERE clause with TRUNCATE command. It removes all table rows at a time. As minimal logging is being done in this command so is quite faster than DELETE command.
It removes the table data by deallocating the data pages that are used to store records or table data. It also checks that if any identity column is being used then whether it has been reset to its seed value or not. You may need at least ALTER table permission in order to use this command or statement. It cannot be used with indexed views and uses less transaction space.
Both of the commands like DELETE and TRUNCATE are used for deletion of tables and its records but there is the difference between both the commands. Though it may be clear to you that what these commands are now we will point out the common differences between both of the commands that are listed below in the following table.
|It is a DML command||It is a DDL command|
|The command is used by using row lock operation||The command is used and executed with table lock operation to remove all the records|
|To filter any specific row or data we can use WHERE clause||We cannot use WHERE clause with this command|
|A log is maintained in this command so is a slower command||No log is maintained so is comparatively faster|
|Rows are removed on eating a time in this command and for each delete operation a transaction log entry is being done||It removes the data by deallocating the pages that are used to store data and only records the pages that are deallocated in the transaction log.|
|DELETE operation retain the table identity||The column is reset to the seed value if any identity column is there in the table|
|You need DELETE permission for the table to use this operation||You may only need ALTER permission to perform TRUNCATE operation|
|It uses more transaction space than TRUNCATE operation||It uses less transaction space than DELETE operation|
|It can be used with indexed views||It cannot be used with indexed views|
In this article, we have discussed the commands DELETE and TRUNCATE and have pointed out the differences as well. Apart from these commands, there is one another command to perform this operation and it is DROP command. DROP command is used to remove a table from the database. In this command, no trigger is usually fired. Like TRUNCATE command, it is also a DDL command. DROP operation cannot be rolled back while DELETE and DROP can be rolled back.
There may be cases when one option may be suitable than other like if you need quick result than TRUNCATE option may be better as it takes less time in query execution, while in case if you may need to roll back the table than DELETE may be a better option for you. So, we can say that as per the table properties you can use the one that is suitable for you and your project. Both of the operations are suitable for delete operation.
In this way, we have seen that in SQL there are in all three options to perform the DELETE operation. As per requirement and table size and the properties of each command, a user can use any of these commands to perform the delete operation. Moreover, most of the DBAs prefer TRUNCATE command as it is faster than Drop and DELETE commands. DDL or DML commands are separate and be used by the user as per their requirement. So, in your case use the one that is most suitable for your project.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Receive Latest Materials and Offers on SQL Server Course