DELETE,TRUNCATE AND DROP

DELETE: (DML)

The DELETE query deletes all records from a table of a database without deleting the table schemas like columns, indexes, etc.

Syntax:

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

Few important points:

1. DELETE is a Data Manipulation Language(DML) command.
2. DELETE is executed using a row lock mechanism, each row in the table is locked for deletion.
3. WHERE clause can be used with DELETE query to filter out records and then delete them.
4. DELETE maintains an entry in the transaction log for each row deleted. Hence it is slower than TRUNCATE.
5. DELETE permission is required on the table to delete the records.
6. The DELETE can be used with indexed views.

Example:

Note:rollback will work but before doing rollback see that the database created with data is commit

TRUNCATE: (DDL)
The TRUNCATE TABLE the command deletes the data inside a table, but not the table itself. TRUNCATE deletes all the rows of a table at once. It only logs once in the transaction log.

Syntax:
TRUNCATE TABLE table_name;

Few important points:

1. TRUNCATE is a Data Definition Language(DDL) command.
2. TRUNCATE is executed using a table lock and the whole table is locked to remove all records.
3. TRUNCATE removes all rows from a table at once.
4. WHERE clause cannot be used with TRUNCATE.
5. It is faster performance-wise than DELETE as it only logs once in the transaction log.
6. TRUNCATE cannot be used with indexed views.
7. To use Truncate on a table, ALTER permission is required on the table.

Example:

Note:rollback and where condition will not work even though the data is commit,and more over it deletes all the records at a time 

 

DROP:(DDL)

The DROP TABLE statement is used to drop an existing table in a database. DROP TABLE query removes the table definition and all data, indexes, triggers, constraints, and permissions for that table.

Syntax:
DROP TABLE table_name;

Few important points:

1. The DROP command removes a table from the database.
2. All the tables’ rows, indexes, and privileges will also be removed.
3. No Data Manipulation Language(DML) triggers will be fired.
4. DROP is a Data Definition Language(DDL).
5. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Example:

Note:rollback and where condition will not work even though the data is commit,and more over it (drops the table)deletes all the records and fields at a time 

Note: Is it possible to re-back the Dropped table the answer is Yes 

How to re-back the Dropped table