Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

Delete vs Truncate SQL Server – What are the Differences?

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.

SQL Server Curriculum

Comparison of SQL Delete and Truncate commands in brief

Before we dive deep into the topic, let us have a quick look at the differences –

  • 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.
  • TRUNCATE command is faster than DELETE operation as it does not use transaction logs
  • If a table is referenced by Foreign key than in that case 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.

Read: Job Roles and Responsibilities of a SQL Server Developer you Need to Know

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 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:

  • 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 Quiz

Read: SSIS Tutorial for Beginners

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.

Comparison of SQL Delete and Truncate commands in Detail

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.

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 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.

Read: How to use SQL Count() Aggregate Function

SQL Server training

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.

Final Words:

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.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

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

Upcoming Class

4 days 24 Nov 2019

DevOps

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

Upcoming Class

5 days 25 Nov 2019

Data Science

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

Upcoming Class

5 days 25 Nov 2019

Hadoop

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

Upcoming Class

6 days 26 Nov 2019

Salesforce

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

Upcoming Class

14 days 04 Dec 2019

Course for testing

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

Upcoming Class

34 days 24 Dec 2019

QA

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

Upcoming Class

13 days 03 Dec 2019

Business Analyst

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

Upcoming Class

5 days 25 Nov 2019

SQL Server

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

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews