Difference between DELETE and TRUNCATE commands in SQL Server

Introduction: Hello Guys, “Welcome back” Today, I am here with another one new great article. In this article I explained the difference between DELETE and TRUNCATE commands in SQL Server database.

Join our Channel  on Whtsapp and Telagram for All Updates                                          

Database Details

I created the following table named userdetail with the all details as follows.

 Column name           data Type

 userid                                   int

 username                 varchar(100)

 useremail                 varchar(50)

useraddress             varchar(250)

I have already inserted few records in the table.

userid       username                 useremail                        useraddress                                       

1                   Sanjeev                  sanjeev@gmail.com    #250 Sector 127, Chandigarh         

2                   Sanjay                    sanjay@gmail.com       #123 Sector 255, Chandigarh         

3                   Ruhika                    ruhika@gmail.com       #256 Sector 855, Chandigarh         

4                   Rajan                      rajan@gmail.com         #55 Sector 00, Chandigarh              

 

Here I am also giving SQL Server Database Script, you can execute this SQL Database Script by using copy and paste to create table with column and data.

         SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[userdetails](

            [userid] [int] IDENTITY(1,1) NOT NULL,

            [username] [varchar](100) NOT NULL,

            [useremail] [varchar](50) NOT NULL,

            [useraddress] [varchar](250) NOT NULL

           CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED

(

            [userid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

I have already inserted few records in the table.

INSERT INTO userdetails

SELECT 'Sanjeev', 'sanjeen@gmail.com', ‘#250 Sector 127, Chandigarh’

 

UNION ALL

 

SELECT 'Sanjay', 'sanjay@gmail.com', ‘#751 Sector 186, Chandigarh’

 

UNION ALL

 

SELECT 'Ruhika', 'ruhika@gmail.com', ‘#256 Sector 855, Chandigarh’

 

UNION ALL

 

SELECT 'Rajan', 'rajan@gmail.com', ‘#55 Sector 00, Chandigarh’

DELETE Statement

DELETE statement in the SQL Server is used to remove or delete rows from a table based on the given condition. It is a DML (Data Manipulation Language) command and it is used when we will given the row as per the condition  that we want to remove or delete from the table. DELETE command can contain a WHERE clause. If the WHERE clause is used with the DELETE command, then it removes or deletes only those rows that satisfy the condition. Otherwise by default, it will remove or delete all the available rows in the table. DELETE command will only for deleting data from a table, not to remove the table from the database.

Syntax of Delete Statement

DELETE FROM TableName WHERE Condition;

 

The DELETE statement will remove specific rows that match a condition.

For example:

 

DELETE FROM userdetails  WHERE userid = 2;

 

If you will not give any specific condition then it will remove all the available rows in the table.

 

DELETE FROM userdetails;

 

Key Characteristics of DELETE Statement

Transaction Log – A DELETE statement in a relational database system, such as SQL Server, interacts significantly with the transaction log. Each row deletion is logged in the transaction log, which is crucial for maintaining data integrity and enabling recovery.

Trigger Activation – A DELETE statement activates a delete trigger, which is a stored procedure that automatically runs in response to a DELETE operation on a table. Triggers can be defined to run "BEFORE" or "AFTER" the deletion to perform actions such as logging changes, updating related tables, or enforcing data integrity rules, preventing data loss.

Performance – The performance of a DELETE statement in a database can be influenced by several factors. Understanding these factors and implementing best practices can significantly improve efficiency..

Space Deallocation – In SQL Server, a DELETE statement on its own doesn't always deallocate space, especially for heap tables (tables without a clustered index), as empty pages remain allocated. To reclaim this space, use a TABLOCK hint in the DELETE statement, perform a table rebuild, or convert the heap to a clustered table. For removing all rows efficiently and deallocating space, TRUNCATE TABLE is the fastest option but does not fire triggers and is less flexible than DELETE.

Foreign Key Constraints – Foreign key constraints define relationships between tables in a relational database, ensuring referential integrity. When a DELETE statement is executed on a row in the parent table (the table containing the primary key referenced by the foreign key), the action taken on dependent rows in the child table (the table containing the foreign key) is determined by the ON DELETE clause specified in the foreign key constraint. 

TRUNCATE Statement

In SQL Server, the TRUNCATE TABLE statement is used to remove all rows from a table quickly and efficiently, while keeping the table structure intact. It is a Data Definition Language (DDL) command. The TRUNCATE statement is used to remove all rows from a table quickly and efficiently.

it is used to delete all the rows or tuples from a table. Unlike the DELETE command, the TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data is not recorded. Unlike the DELETE command, the TRUNCATE command is fast. We cannot roll back the data after using the TRUNCATE command. TRUNCATE statement deletes all data from Table without resetting the Table's identity to its seed value.

Syntax

TRUNCATE TABLE TableName;

TRUNCATE statement removes all rows from a table without the need for a condition.

TRUNCATE TABLE userdetails;

Key Characteristics of TRUNCATE Statement

Transaction Log – In SQL Server, "truncating the transaction log" refers to the process of deleting inactive virtual log files (VLFs) from the logical transaction log. This action frees up space within the logical log, making it available for reuse by the physical transaction log file. This process is crucial for managing the size of the transaction log and preventing it from consuming excessive disk space.

Trigger Activation – In SQL Server, TRUNCATE TABLE statements do not activate DML triggers (INSERT, UPDATE, DELETE triggers). This is because TRUNCATE TABLE is a DDL (Data Definition Language) command that deallocates data pages rather than logging individual row deletions, which is what DML triggers rely on.

Performance – In SQL Server, the TRUNCATE TABLE statement offers significant performance advantages over DELETE for removing all rows from a table. These advantages stem from its underlying mechanism. Because TRUNCATE is minimally logged and does not scan individual rows, it is generally faster than DELETE for large tables.

Space Deallocation – In SQL Server, the TRUNCATE TABLE statement is a Data Definition Language (DDL) command used to remove all rows from a table, and it immediately deallocates the space occupied by those rows. This deallocated space is then available for reuse within the database..

Foreign Key Constraints – In SQL Server, a TRUNCATE TABLE statement cannot be directly executed on a table that is referenced by a foreign key constraint. This is because TRUNCATE TABLE is a Data Definition Language (DDL) command that bypasses row-by-row logging and checks, which are necessary to maintain referential integrity enforced by foreign keys. Attempting to truncate such a table will result in an error indicating that the table is referenced by a foreign key constraint.

Reseed Identity Column – When using TRUNCATE TABLE in SQL Server, the identity column's counter is automatically reset to its original seed value. If no seed value was explicitly defined during table creation, the default seed value of 1 is used. To reseed the identity column to a different value after a TRUNCATE TABLE operation, use the DBCC CHECKIDENT command. When TRUNCATE is used, the identity column (if present) is reset to its seed value. For example, if the table has an identity column starting at 1, it will restart at 1 after truncation.

Conclusion

DELETE and TRUNCATE depends on the specific requirements of your operation and you can remove or delete the rows correctly by using the DELETE and TRUNCATE commands. You need to Use DELETE command when you need to remove specific rows, respect foreign key constraints, or activate triggers. The TRUNCATE command must be used with caution because it deletes all of the table’s records and when you need to quickly remove all rows from a table and reclaim space efficiently, and when there are no foreign key constraints to consider. It totally depends on the user requirement, for removing specific rows, use DELETE. And for remove all rows from a large table and leave the table structure, use TRUNCATE TABLE. It’s faster than DELETE.

In above code, I explained the difference between DELETE and TRUNCATE commands in SQL Server database. Bye and take care of yourself Developers. We will come back shortly with the new article.

 

Regards

Programming Hub