Difference Between
DELETE, TRUNCATE, And DROP Command:
DELETE:
SQL DELETE query deletes all records from a database
table. To execute a DELETE query, delete permissions are required on the target
table. If you need to use a WHERE clause in a DELETE, select permissions are
required as well.
DELETE and TRUNCATE command work similarly but the
major difference is that DELETE command can use WHERE Clause but TRUNCATE
command can’t use WHERE clause and TRUNCATE remove all record from table using
a single query.
Syntax:
DELETE
FROM table_name
WHERE
[condition];
You
can combine N number of conditions using AND or OR operators.
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Khilan | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Hardik | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
Following is an
example, which
would DELETE a customer, whose ID is 6:
SQL>
DELETE FROM CUSTOMERS
WHERE
ID = 6;
Now,
CUSTOMERS table would have the following records:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Khilan | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Hardik | 27
| Bhopal | 8500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
If you
want to DELETE all the records from CUSTOMERS table, you do not need to use
WHERE clause and DELETE query would be as follows:
SQL>
DELETE FROM CUSTOMERS;
Now, CUSTOMERS
table would not have any record.
- DELETE is a DML (Data
Manipulation Language) command.
- DELETE is executed using a row lock,
each row in the table is locked for deletion.
- We can use where clause with DELETE
to filter & delete specific records.
- The DELETE command is used to remove
rows from a table based on WHERE condition.
- It maintains the log, so it slower
than TRUNCATE.
- The DELETE statement removes rows
one at a time and records an entry in the transaction log for each deleted
row.
- Identity of column keep DELETE
retains the identity.
- To use Delete you need DELETE
permission on the table.
- Delete uses more transaction space
than the Truncate Command.
- The delete can be used with indexed
views.
TRUNCATE:
TRUNCATE SQL query removes all rows from a table,
without logging the individual row deletions.
NOTE - TRUNCATE is faster than the DELETE query.
Syntax:
TRUNCATE
TABLE table_name;
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Ramesh | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Chaitali | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
Following is the
example to
truncate:
SQL
> TRUNCATE TABLE CUSTOMERS;
Now,
CUSTOMERS table is truncated and following would be the output from SELECT
statement:
SQL>
SELECT * FROM CUSTOMERS;
Empty set (0.00
sec)
- TRUNCATE is a DDL (Data
Definition Language) command
- TRUNCATE is executed using a table
lock and the whole table is locked to remove all records.
- We cannot use the WHERE clause with
TRUNCATE.
- TRUNCATE removes all rows from a
table.
- Minimal logging in the transaction
log, so it is faster performance-wise.
- TRUNCATE TABLE removes the data by
de-allocating the data pages used to store the table data and records only
the page de-allocations in the transaction log.
- Identify the column is reset to its
seed value if the table contains an identity column.
- To use Truncate on a table you need
at least ALTER permission on the table.
- Truncate uses less transaction space
than the Delete statement.
- Truncate cannot be used with indexed
views.
- TRUNCATE is faster than DELETE.
DROP:
DROP table query removes one or more table
definitions and all data, indexes, triggers, constraints, and permission
specifications for those tables. DROP command requires to ALTER permission on
the schema to which the table belongs, CONTROL permission on the table, or
membership in the db_ddladmin fixed database role.
Syntax:
DROP TABLE tableName;
Following is the example DROP CUSTOMERS table permanently from the database,
Once CUSTOMERS table deleted from database, It can’t be recovered.
SQL > DROP TABLE CUSTOMERS;
Query Ok – Table Dropped
- The DROP command removes a table
from the database.
- All the tables' rows, indexes, and
privileges will also be removed.
- No DML triggers will be fired.
- The operation cannot be rolled back.
- DROP and TRUNCATE are DDL (Data
Definition Language) commands, whereas DELETE is a DML (Data
Manipulation Language) command.
- DELETE operations can be rolled
back (Undone), while DROP and TRUNCATE operations cannot be rolled
back
0 Comments