Jun 28, 2018 
537.6k
29
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
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. By taking a glance at Delete and Truncate difference, you can easily implement the commands in the SQL Server database. By knowing the truncate vs delete sql server DML operations cannot be rolled back.
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 time used for individual tables 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. The same delete vs truncate oracle are used in tables when information is logged.
Read: How to Ace Your Microsoft Power BI Certification Exam
Basically, you may find two commands in SQL for table data deletion one is DELETE and the other is TRUNCATE. SQL Delete vs Truncate, These two keywords or table data deletion commands are used to delete table data and both of these commands have their 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 and understanding SQL DELETE vs TRUNCATE difference will make it more clear to you.
Before we dive deep into the topic, let us have a quick look at the delete and truncate difference –
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.
Learn SQL Server in the Easiest Way
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 the DELETE command along with where clause. Table rows can also be deleted through the DELETE command as per the 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 the 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.
Read: SQL REPLACE() Function: A Step-By-Step Guide
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 the 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.
SQL Server Training & Certification
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 the foreign key constraint, you cannot use the TRUNCATE command as they cannot fire the triggers as well. So, it may also result in inconsistent data as the transaction log is not maintained in this command.
In case, if there is a foreign key in the table then, in that case, the 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 a table lock in which the whole table is locked before removing the table records. The user cannot use the WHERE clause with the TRUNCATE command. It removes all table rows at a time. As minimal logging is being done in this command, it is quite faster than DELETE command.
Read: What Does It Take To Become a Successful SQL DBA Expert?
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 commands like DELETE and TRUNCATE are used for the 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 named SQL Delete vs Truncate:
| DELETE | TRUNCATE |
| 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 difference and have pointed out the comparisons as well. Apart from these commands, there is one another command to perform this operation and it is the DROP command. DROP command is used to remove a table from the database. In this command, no trigger is usually fired. Like the TRUNCATE command, it is also a DDL command. DROP operation cannot be rolled back while DELETE and DROP can be rolled back.
SQL Server Training & Certification
There may be cases when one option may be suitable than other like if you need quick results than the 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. Hope you are now clear about truncate vs delete sql server.
In this way, we have seen SQL delete vs truncate and came to know 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 the 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.
Read: Top 12 SQL Project Ideas for Beginners
FaceBook
Twitter
LinkedIn
Pinterest
Email
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.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access
271.5k
What are Data Types and Their Usage in SQL Server Tables?
5.7k
How to Clear SQL Server Transaction Log File with DBCC Shrinkfile
216.4k
What is Database? A Definitive Guide
667
What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)
169.2k
Receive Latest Materials and Offers on SQL Server Course
Interviews
Oct 22, 2018
271.5k
