What is the Difference between Delete, Truncate, and Drop command?
The “DELETE” command removes one or more records from a table in the database, and the “TRUNCATE” command removes all rows from a table, but Still, it keeps the table structure, and the “DROP” command completely removes the entire table from the database.
Table of Contents
- 1 Delete vs Truncate vs Drop command in SQL Server:
- 2 What is the DELETE Command in SQL?
- 3 What is the TRUNCATE Command in SQL?
- 4 What is Drop Command?
- 5 FAQs – SQL Delete vs Truncate vs Drop:
- 5.1 Q: What is the difference between DELETE, TRUNCATE and DROP commands in SQL?
- 5.2 Q: Can you Rollback the DELETE command?
- 5.3 Q: Can you Rollback the TRUNCATE command?
- 5.4 Q: Can you Rollback undo the DROP command?
- 5.5 Q: Is the DELETE command faster than the TRUNCATE command?
- 5.6 Q: Can you use the WHERE clause with the TRUNCATE command?
- 5.7 Q: How does DROP command differ from the TRUNCATE command?
- 5.8 Q: Can you use DELETE, TRUNCATE, and DROP commands on indexed views?
- 5.9 Related
Delete vs Truncate vs Drop command in SQL Server:
Here, we can see the basic differences between the Delete, Truncate, and Drop commands in SQL.
|Definition:||The “DELETE” command removes one or more records from a table in the database.||The “DROP” command completely removes the table from the database.||The “Truncate” command removes all the records from the table but keeping the table’s structure untouched.|
|Syntax:||DELETE FROM table_name WHERE conditions;||DROP TABLE table_name;||TRUNCATE TABLE table_name;|
|Command Type:||“DELETE” is a DML (Data Manipulation Language) command.||“DROP” is a DDL (Data Definition Language) command.||“Truncate” is a DDL (Data Definition Language) command.|
|Rollback:||The DELETE command provides a way to recover deleted data using “ROLLBACK.” If a row is accidentally deleted from the database, you can retrieve it again using this command.||The DROP command can’t be rolled back.||The TRUNCATE command can’t be rolled back.|
|Performance:||The “DELETE” command is slower compared to both “DROP” and “TRUNCATE” as it deletes rows one at a time based on specified conditions.||The “DROP” command is relatively faster than the “DELETE” command but slower than “TRUNCATE” as it first deletes the rows and then the entire table from the database.||“TRUNCATE” is the quickest of the three commands. It quickly removes all records from a table without the need for any specific conditions.|
|Logging:||The SQL DELETE command logs the data for each row that is deleted.||The SQL Drop command doesn’t log the data.||It doesn’t log the data.|
|Using a WHERE clause:|
|Foreign key constraints:||The SQL DELETE command checks the foreign key constraints while deleting rows.||The DROP Command doesn’t check the foreign key constraints.||The SQL TRUNCATE command doesn’t check the foreign key constraints.|
|Identity column:||SQL DROP command doesn’t reset the Identity column.||The DROP command fully deletes the table from the database, so resetting the Identity column doesn’t make sense.||The TRUNCATE command resets the Identity column.|
|Permission:||It requires the DELETE permission on the table.||It requires ALTER permission on the schema to which the table belongs and CONTROL permission on the table.||It requires ALTER permission on the table.|
|Memory management:||The delete command does not free up the memory space allocated to the table.||The Drop command releases the memory space allocated to the table.||The truncate command does not release the memory space allocated to the table.|
The following is the SQL script to create the Customers table with sample data. We will use this table to learn how to use
CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(50) NOT NULL ); INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES (1, 'Shekh', 'Ali', 'email@example.com'), (2, 'Sadaf', 'Kamal', 'firstname.lastname@example.org'), (3, 'Jack', 'Smith', 'email@example.com'), (4, 'Adam', 'Johnson', 'firstname.lastname@example.org');
What is the DELETE Command in SQL?
DELETE command in SQL is used to delete specific rows from a table. It deletes one or more rows based on a specified condition using the
The DELETE is a DML (Data Manipulation Language) command, which can be rolled back if required.
The DELETE statement removes records from a table, but does not delete the table itself and keeps the record of each deleted row in a transaction log.
The following is the syntax of SQL DELETE Command:
DELETE FROM TableName WHERE condition;
Example: DELETE Command
The below SQL query will delete the row from the Customers table where the
CustomerID is 3.
DELETE FROM Customers WHERE CustomerID = 3;
Customer table will keep only the following records after we run the above SQL Delete query.
Customers table has many rows. The above SQL query filters and finds the row with
CustomerId 3. Then, the
DELETE command removes the record of customer “Jack Smith” from the table, as that row matches the condition.
Important Facts about the SQL DELETE Command:
- The DELETE command is used in SQL to delete specific rows from a table based on a given where condition. It’s like picking out particular rows from a table and deleting them while leaving the rest records untouched.
- Imagine a table has huge records with rows and columns. When you use the DELETE command, you tell SQL which rows to find and delete based on some conditions you specify.
- For example, you can say, “Hey SQL, please delete all rows where the ‘age’ column is less than 18,” SQL will remove all rows of people under 18 from the table.
- The DELETE command is part of Data Manipulation Language (DML), which means it deals with modifying the data in the table.
- One important thing to know about the DELETE command is that it can be rolled back. It means if you accidentally delete something you didn’t mean to, you can undo the action and bring the deleted data back.
- The delete command uses the WHERE clause to delete specific rows from the table and locks each row before deletion.
- Delete command is slower than TRUNCATE as it keeps a log of deleted rows.
What is the TRUNCATE Command in SQL?
TRUNCATE is a DDL (Data Definition Language) command in SQL that removes all the data from a table in a database. It works like a “reset” button for the table, deleting all rows while preserving its structure with the column’s name.
Unlike the DELETE command, which removes specific rows based on conditions, TRUNCATE empties the entire table at once.
However, it’s important to note that TRUNCATE cannot be rolled back, so any data removed using TRUNCATE command is permanently deleted.
Note: The TRUNCATE command doesn’t need a WHERE clause like DELETE. It only requires the table name to delete records and performs better because it skips condition checking.
After executing the TRUNCATE command, the only thing that remains is the table structure with column’s name.
The syntax for the SQL TRUNCATE command is as follows:
TRUNCATE TABLE TableName;
Example: TRUNCATE Command
When you run the below SQL TRUNCATE command, it will erase all data from the Customers table, leaving only the column names. All existing rows will be instantly removed.
TRUNCATE TABLE Customers;
Important Facts about the SQL TRUNCATE Command:
TRUNCATEcommand in SQL is used to remove all the data from a table completely. It’s like taking a big eraser and wiping out all the table’s contents but keeping the table’s structure (the rows and columns) untouched.
- When you use TRUNCATE, you’re telling SQL to forget about all the data stored in the table, and the table becomes empty with zero rows. It’s a quick way to eliminate lots of data in one go.
- TRUNCATE is also part of Data Definition Language (DDL), which deals with defining or altering the structure of the database.
- Unlike DELETE, TRUNCATE cannot be rolled back. Once you use TRUNCATE, the data is permanently gone. So, you must be careful while using it, as there’s no way to get the data back once it’s truncated.
- Unlike the DELETE command, TRUNCATE doesn’t support using the WHERE clause to specify specific rows for deletion.
- One significant advantage of TRUNCATE is its minimal logging in the transaction log, which makes it much faster than DELETE.
- The TRUNCATE command deallocates the data pages used to store the table data, resulting in improved performance and reduced resource usage.
- TRUNCATE resets the Identity column (if present) to its seed value, ensuring that the auto-increment feature starts from the beginning when new data is added.
- One limitation of TRUNCATE is that it cannot be used with indexed views. If the table is associated with indexed views, you must resort to DELETE instead.
- However, remember that using TRUNCATE requires the user to have ALTER permission on the table. Without this permission, the TRUNCATE command won’t execute.
What is Drop Command?
DROP command is used to delete a table or other objects (indexes, triggers, constraints) from a database. It is a DDL ( Data Definition Language) command and it permanently removes the table and its data from the database. It cannot be rolled back.
The Drop command does not require any conditions to be specified using a ‘where’ clause, only the table name is necessary. Once the command is executed, it permanently removes the entire table structure, and the data cannot be rollback or recovered.
The syntax for the SQL DROP command is as follows:
DROP TABLE TableName;
Example: DROP Command
The below SQL query will delete the Customers table and all its data permanently from the database.
After a table is dropped, it cannot be recovered. The Drop command is irreversible and there is no option for rollback.
DROP TABLE Customers;
Important Facts about the SQL DROP Command:
DROPcommand in SQL allows you to delete entire table or even other database objects like views or indexes. It’s like removing an entire table from the database.
- When you use the DROP command, you’re telling SQL to completely delete the table and all its data (rows and columns).
- Like TRUNCATE, DROP is also part of Data Definition Language (DDL) because it changes the database’s structure by removing objects.
- Just like TRUNCATE, the DROP command cannot be rolled back. Once you use DROP, the table and all its data are gone forever.
- So, be very cautious when using the DROP command, as you can accidentally erase important data if you’re not careful.
Remember, when working with databases, always double-check your commands and back up your data to prevent accidental loss.
References: c-sharp corner– Difference between Truncate, Delete, and Drop Command
FAQs – SQL Delete vs Truncate vs Drop:
Q: What is the difference between DELETE, TRUNCATE and DROP commands in SQL?
The DELETE command is used to remove specific rows from a table based on a WHERE condition.
The TRUNCATE command is used to remove all data from a table, but unlike DELETE, it is much faster as it does not maintain a log of deleted rows.
The DROP command is used to completely remove a table from the database, along with all its rows, indexes, and privileges.
Q: Can you Rollback the DELETE command?
Yes, DELETE operations can be rolled back, meaning that the deleted records can be restored.
Q: Can you Rollback the TRUNCATE command?
No, TRUNCATE operations cannot be rolled back, meaning that deleted records can’t be restored.
Q: Can you Rollback undo the DROP command?
No, DROP operations cannot be rolled back, meaning that the table, along with all its data, indexes, and privileges, cannot be recovered once it is dropped.
Q: Is the DELETE command faster than the TRUNCATE command?
No, TRUNCATE is faster than DELETE as it does not maintain a log of deleted rows.
Q: Can you use the WHERE clause with the TRUNCATE command?
No, the TRUNCATE command removes all data from a table and it doesn’t use the WHERE clause to filter specific rows.
Q: How does DROP command differ from the TRUNCATE command?
DROP removes a table and everything in it completely from the database. TRUNCATE only removes all the data inside the table but the table itself still exists in the database.
Q: Can you use DELETE, TRUNCATE, and DROP commands on indexed views?
No, TRUNCATE and DROP commands are not allowed on indexed views but you can use the DELETE command on indexed views.
You might want to read this too:
- Having VS Where Clause in SQL
- SQL Server Indexing: Clustered vs Non Clustered Index Explained
- Understanding COALESCE in SQL With Examples and Use Cases
- Types of Joins in SQL Server
- Function vs Stored procedure in SQL Server
- Stored Procedure in SQL Server With Examples
- Create, Alter, and Drop Database In SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
- Primary Key Constraint In SQL Server
- View In SQL | Types Of Views In SQL Server
- SQL Server Trigger Update, Insert, Delete Examples
- SQL pivot tables: Understanding pivot tables in SQL Server
- C# Program to Convert Binary to Decimal with Examples - December 4, 2023
- C# Program to Print Multiplication Table of a Given Number - November 30, 2023
- Palindrome program in C# with examples - November 28, 2023