The Detailed Difference and discussion is as follows

DROP
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).
Syntax:
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
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).
Syntax:
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.


Queries
  • 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.

0 Comments:

Post a Comment