DROP and TRUNCATE in SQL

The DROP and TRUNCATE commands in SQL are used to remove data from a table, but they work differently. This difference is crucial for effective database management, especially with large datasets.

SQL DROP Command

The SQL DROP command is used to permanently remove an object from a database, such as a table, database, index or view. When a table is dropped, both its data and structure are permanently removed, and the action cannot be undone without a backup.

• Permanent Removal: Deletes object and data permanently.
• Deletes Dependencies: Removes related constraints and indexes.
• Schema Deleted: Removes table structure and schema.
• No Integrity Check: Skips constraints and triggers.

Syntax:

DROP object object_name ;

Key Terms

• object: The type of object you want to drop (e.g., TABLE, DATABASE).
• object_name: The name of the object to be deleted.

Examples of Using the DROP Command

To understand how the DROP command works in SQL, let us explore some practical examples demonstrating its various applications.

1. DROP Table

To delete an entire table including its data and structure:

Syntax:

DROP TABLE table_name;

2. DROP database

To delete an entire database and all of its associated tables:

Syntax:

DROP DATABASE database_name;

SQL TRUNCATE Command

The TRUNCATE command is a Data Definition Language (DDL) action that removes all rows from a table but preserves the structure of the table for future use.

Although TRUNCATE is similar to the DELETE command (without the WHERE clause), it is much faster because it bypasses certain integrity constraints and locks. It was officially introduced in the SQL:2008 standard.

Syntax:

TRUNCATE TABLE  table_name;

Key Terms

• table_name: Name of the table to be truncated.
• DATABASE name: student_data

Key Differences Between DROP and TRUNCATE

The key differences between DROP and TRUNCATE statements are explained in the following table:

Examples of SQL DROP AND TRUNCATE Command

Let's look at some examples of the DROP and TRUNCATE statements in SQL and understand their working. Consider the given database Student_data We will perform the examples on this particular table.

DROP Database Example

In this example, we will drop a database called student_data:

DROP DATABASE student_data; 

Running this query will completely delete the student_data database, including all tables and data.

DROP Table Example

In this example, we will drop the student_details table;

DROP TABLE student_details; 

This query will permanently remove the student_details table, along with all its data, from the database.

TRUNCATE Table Example

In this example, we will truncate all data from a table (e.g., student_details) while keeping the table structure as it is:

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.