DROP is used to delete a whole database or just a table.The DROP statement destroys the objects like an existing database, table, index, or view.
A DROP statement in SQL removes a component from a relational database management system (RDBMS).
DROP object object_name Examples: DROP TABLE table_name; table_name: Name of the table to be deleted. DROP DATABASE database_name; database_name: Name of the database to be deleted.
TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard.
The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).
TRUNCATE TABLE table_name; table_name: Name of the table to be truncated. DATABASE name - student_data
DROP vs TRUNCATE
- Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
- Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
- Table or Database deletion using DROP statement cannot be rolled back, so it must be used wisely.
- To delete the whole database
DROP DATABASE student_data;
After running the above query whole database will be deleted.
- To truncate Student_details table from student_data database.
TRUNCATE TABLE Student_details;
After running the above query Student_details table will be truncated, i.e, the data will be deleted but the structure will remain in the memory for further operations.
This article is contributed by Pratik Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between DROP and TRUNCATE in SQL
- Difference between DELETE, DROP and TRUNCATE
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | ALTER (ADD, DROP, MODIFY)
- Difference between DELETE and DROP in SQL
- Create, Alter and Drop schema in MS SQL Server
- Drop login in SQL Server
- CREATE and DROP INDEX Statement in SQL
- DROP SCHEMA in SQL Server
- Difference between DELETE and TRUNCATE
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- MySQL | DROP USER
- Neo4j Drop Index
- DROP FUNCTION and its Parameters
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL | SELECT Query
- SQL | Distinct Clause