Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

Delete vs Truncate SQL Server – What are the Differences?



Introduction

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: Coalesce Function SQL Server Example

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.

Comparison of SQL Delete and Truncate commands in brief

Before we dive deep into the topic, let us have a quick look at the delete and truncate difference –

  • Identity values are reseeded by TRUNCATE operation while this is not possible by using the DELETE operation.
  • The trigger is not fired in TRUNCATE operation and all values are removed through this operation.
  • The TRUNCATE command is faster than DELETE operation as it does not use transaction logs
  • If a table is referenced by Foreign key then TRUNCATE cannot be used.

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

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

What is SQL Delete Command?

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: How To Become SQL Certified Professional To Grow Your Career?

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:

  • DELETE from “XYZ_table”;
  • DBCC CHECKIDENT (“XYZ_table”, RESEED, “reseed_value”)

What is SQL Truncate Command?

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

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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: Top 50 MongoDB Interview Questions and Answers

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.

Comparison of SQL Delete and Truncate commands in Detail

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

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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.

Final Words:

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 50 MongoDB Interview Questions and Answers

SQL Tutorial Overview


    Janbask Training

    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.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 14 Jul 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-0 day 10 Jul 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

6 days 16 Jul 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 17 Jul 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

5 days 15 Jul 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

-0 day 10 Jul 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

4 days 14 Jul 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

5 days 15 Jul 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

13 days 23 Jul 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

4 days 14 Jul 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

7 days 17 Jul 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

3 days 13 Jul 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews