Difference between Delete , Truncate & Drop in Microsoft SQL Server
0
9
0
DROP, TRUNCATE, and DELETE are SQL commands used to manipulate data within a database, but they serve different purposes and have distinct characteristics:
DROP:
Purpose: Used to remove an entire table or database from the database schema.
Syntax: DROP TABLE table_name; or DROP DATABASE database_name;
Operation: Completely deletes the table or database along with all the data, constraints, indexes, triggers, and permission specifications.
Rollback: Cannot be rolled back (irreversible).
Usage Example:DROP TABLE Employees; TRUNCATE:
Purpose: Used to delete all rows from a table without logging individual row deletions.
Syntax: TRUNCATE TABLE table_name;
Operation: Removes all rows from a table but retains the table structure and its columns, constraints, and indexes.
Rollback: Can be rolled back if used within a transaction (in databases that support transactional TRUNCATE).
Usage Example:TRUNCATE TABLE Employees; DELETE:
Purpose: Used to delete specific rows from a table based on a condition.
Syntax: DELETE FROM table_name WHERE condition;
Operation: Deletes rows one at a time and logs each row deletion, which can be time-consuming for large tables.
Rollback: Can be rolled back if used within a transaction.
Usage Example:DELETE FROM Employees WHERE EmployeeID = 1234;
Key Differences:
Scope:
DROP removes the entire table or database.
TRUNCATE removes all rows from a table but keeps the table structure.
DELETE removes specific rows based on a condition. Logging and Performance:
DROP is not logged; it's a schema modification operation.
TRUNCATE is minimally logged for faster performance but doesn't log individual row deletions.
DELETE is fully logged and slower for large tables due to logging each row deletion. Constraints and Indexes:
DROP removes everything related to the table.
TRUNCATE keeps the table structure, constraints, and indexes.
DELETE keeps everything related to the table and affects only the rows. Rollback:
DROP cannot be rolled back.
TRUNCATE can be rolled back in transactional contexts.
DELETE can always be rolled back in transactional contexts.
Related Posts
