Difference between Truncate and Delete in SQL Server

Kiran Kumar

1y1.1kSQL Server

What is the difference between Truncate and Delete in SQL Server

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nidhi Kumari1y ago

Accepted answer

Difference Between TRUNCATE and DELETE in SQL Server

Aspect	TRUNCATE	DELETE
Operation Type	DDL (Data Definition Language)	DML (Data Manipulation Language)
Purpose	Removes all rows from a table quickly.	Deletes specific rows or all rows based on a `WHERE` condition.
Condition Support	Does not support `WHERE` clause.	Supports `WHERE` clause to filter rows for deletion.
Performance	Faster because it doesn't log individual row deletions.	Slower as each row deletion is logged in the transaction log.
Logging	Minimal logging for performance; logs table structure changes.	Fully logs each row deletion for rollback.
Triggers	Does not activate triggers.	Activates triggers if defined on the table.
Identity Reset	Resets the identity column to its seed value.	Does not reset the identity column.
Rollback	Can be rolled back if inside a transaction.	Can be rolled back if inside a transaction.
Table Locking	Acquires a schema modification lock (locks entire table).	Acquires row-level locks if `WHERE` is used; otherwise, locks table.
Usage Scenario	Best for clearing all data from a table quickly.	Used when specific rows need to be deleted.

Key Points:

Use TRUNCATE when you want to quickly remove all rows and reset the table to its initial state.
Use DELETE when you need control over which rows are removed.

Tuhin Paul1y ago
cc
0

Shubham Sidnale1y ago

The TRUNCATE and DELETE commands in SQL Server are both used to remove data from a table, but they have key differences in terms of functionality, performance, and how they affect the database.

Key Differences Between TRUNCATE and DELETE

Aspect	TRUNCATE	DELETE
Type of Command	DDL (Data Definition Language)	DML (Data Manipulation Language)
Usage	Removes all rows from a table.	Removes specific rows based on a condition or all rows if no condition is given.
WHERE Clause	Not allowed (removes all rows).	Allowed (can delete specific rows).
Logging	Minimal logging for performance.	Fully logged, each deleted row is logged.
Performance	Faster due to minimal logging and no row-by-row operations.	Slower due to row-by-row operations and full logging.
Impact on Table Schema	Resets identity columns (if any) to their seed value.	Does not reset identity columns.
Triggers	Does not fire triggers.	Fires triggers associated with DELETE.
Rollback Support	Can be rolled back if within a transaction.	Can be rolled back if within a transaction.
Foreign Key Constraints	Cannot truncate a table with foreign key constraints.	Can delete rows from a table with foreign key constraints if the constraints are satisfied.

When to Use TRUNCATE

When you need to quickly remove all data from a table.
When you do not need to trigger DELETE triggers.
When you want to reset identity columns.

When to Use DELETE

When you need to remove specific rows using a condition (e.g., DELETE FROM Employees WHERE Age > 50).
When DELETE triggers are required.
When the table is referenced by foreign key constraints, and you must carefully manage data removal.

Example Scenarios

TRUNCATE Example

-- Removes all rows from the 'Orders' table and resets any identity column

TRUNCATE TABLE Orders;

DELETE Example

-- Deletes only rows where the order status is 'Completed'

DELETE FROM Orders WHERE Status = 'Completed';

Conclusion

Use TRUNCATE when you want to remove all data quickly and efficiently, and table constraints allow it.
Use DELETE when you need granular control over which rows to remove or when working with foreign key constraints and triggers.

Rajanikant Hawaldar1y ago
https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1
0
Ananthakrishna V1y ago
TRUNCATE removes all rows from a table without using a WHERE clause. DELETE can remove specific rows using a WHERE clause. DELETE activates triggers; TRUNCATE does not.
0

Vijay Pratap Singh1y ago

TRUNCATE:
Use when you want to quickly remove all rows from a table and reset the identity values, and you do not need to selectively delete rows or maintain a detailed transaction log.
DELETE:
Use when you need to selectively remove rows using conditions, or when dealing with tables that have foreign key constraints.

-- Create a table for demonstration
CREATE TABLE Employees (
    ID INT IDENTITY(1,1),
    Name NVARCHAR(50)
);

-- Insert some data
INSERT INTO Employees (Name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- View data
SELECT * FROM Employees;

-- Truncate the table
TRUNCATE TABLE Employees;

-- Check the table (should be empty)
SELECT * FROM Employees;

-- Note: The identity column is reset
INSERT INTO Employees (Name) VALUES ('David');
SELECT * FROM Employees; -- ID starts from 1 again

DELETE

-- Create a table for demonstration
CREATE TABLE Employees (
    ID INT IDENTITY(1,1),
    Name NVARCHAR(50)
);

-- Insert some data
INSERT INTO Employees (Name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- View data
SELECT * FROM Employees;

-- Delete a specific row
DELETE FROM Employees WHERE Name = 'Bob';

-- Check remaining rows
SELECT * FROM Employees;

-- Delete all rows (without resetting identity)
DELETE FROM Employees;

-- Insert new data
INSERT INTO Employees (Name) VALUES ('Eve');
SELECT * FROM Employees; -- ID does not reset; starts after the last identity value

Hope this will help

Rakesh Kamath1y ago
The TRUNCATE and DELETE commands in SQL Server are both used to remove data from a table, but they have significant differences in their functionality, performance, and impact on the database. Here's a comparison:

1. Operation Scope
- TRUNCATE: Removes all rows from a table. It operates on the entire table and cannot include a WHERE clause.
- DELETE: Removes specific rows from a table. It allows the use of a WHERE clause to specify which rows to delete.
2. Performance
- TRUNCATE:
  
  Faster because it does not log individual row deletions in the transaction log.
  
  It deallocates the data pages used by the table instead of deleting rows one by one.
- DELETE:
  
  Slower because it logs each deleted row in the transaction log.
  
  It processes row-by-row deletions, which can be time-consuming for large datasets.
3. Impact on Table Structure
- TRUNCATE:
  
  Resets the table’s identity column (if present) to its seed value.
  
  Cannot be used if the table is referenced by a foreign key constraint.
- DELETE:
  
  Does not reset the table’s identity column.
  
  Can delete rows even if the table is referenced by a foreign key, but referential integrity constraints must be satisfied.
0