Difference Between Drop Table and Truncate Table

Difference between drop table and truncate table?

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

TRUNCATE v/s DROP

There is lots of information available on the difference of both ( see here). However for the second part of your question regarding the performance - you should not worry that much for truncate vs drop. The reason being is it's more about the functionality you are expecting from truncate vs drop. You 'drop' when you don't intend to use the table anymore. You 'truncate' when you just want the table definition intact , you just want to delete the table data fast. The perceived difference in performance may come from the implementation details ( which can sometime be a bug like this) in the database.

What is the difference between truncate, drop and delete of tables? And when to choose for which?

Based on an answer by @Michal here and some more searching I made a comparison beneath for the following statements (in t-sql): truncate table TableX, drop table TableXand delete table_name.

                           Truncate           Drop                 Delete
Speed                      [Fast]             Slow                 Slowest
Rolback possibility        No                 No                   [Yes]
Specifiable conditions     No                 No                   [Yes]
Scope                      All records        All record+Headers   Some records/All records
                                              =whole table 
Cascading effects          No*                No*                  [Yes]** 

**For example: in a Table_1 there is a PK, in Table_2 there is a FK that relates with 
the PK of Table_1, other words there is referential integrity. If the PK has `'ON DELETE CASCADE'` 
and `delete Table_1` is ordered, then the data in Table_2 will be deleted too, 
automatically. For more info about ON DELETE CASCADE and ON ALTER CASCADE, see:
https://technet.microsoft.com/en-us/library/ms186973%28v=sql.105%29.aspx. 

Cascading does automatic alterations and deletes of depending objects such as foreign keys (FK), 
views, and triggers. Sometimes very useful, sometimes very dangerous..

*The drop and truncate statements of a Table_1 (with PK and FK in Table_2, as decribed 
in **) can't be executed, because the ssdms prohibits that. To accomplish the truncation 
or dropping of a Table_1: first get rid of the FK in Table_2, by altering the table design, or 
by dropping table2.

See the comparison to base the decision when to use which statement...

As a thumb:

If you want to get rid of only records:
use delete when a conditional deleting is required, use truncate when all records may be get rid of. When you want to be able to rollback then use delete.

If you want to get rid of the whole table, including the headers (columns with settings) then choose drop.

If you want to get rid of values and automatically the related objects (and cascading is defined in the table), use delete. (PS: in other dialects it seems there are ways to accomplish it even when the table is not designed with cascading, but as far as I know there isn't in t-sql/msss; but correct me if I'm wrong)

PS: if you want to alter or delete the preferences of a column, then use (in t-sql dialect):

Alter:

alter table tableX
alter columnX datatypeX

Delete:

alter table tableX
drop column columnX

--And here's some code to play with
--table to truncate, drop or delete

create table TableX(
       [Name] [nchar](25) null,
       [ID_Number] [int] not null)

--tables with PK and FK 
create table Table_1(
       [Name] [nchar](25) null,
       [ID_Number] [int] not null primary key)

create table Table_2(
       [ID_Number] int not null foreign key references Table_1(ID_Number) on delete cascade,
       [Buys] [int] null)

--the on delete cascade make it happen that when a ID_Number is Table_1 is deleted, that row
is automatically deleted in Table_2 too. But not the other way around, 
therefor alter the design of Table_1.

insert into Table_1 (Name,ID_Number) values ('A',1),('B',2),('C',3);
insert into Table_2 (ID_Number,Buys) values (1,10),(2,20),(3,30);

select * from Table_1
select * from Table_2

truncate table table_2
truncate table table_1

drop table table_2
drop table table_1

delete Table_1

delete from dbo.table_1 where name='A'   
delete from Table_1 where name like '%'  
delete from dbo.table_2 where ID_Number=2

What's the difference between TRUNCATE and DELETE in SQL

Here's a list of differences. I've highlighted Oracle-specific features, and hopefully the community can add in other vendors' specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.

General Overview

If you want to quickly delete all of the rows from a table, and you're really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.

Various system-specific issues have to be considered, as detailed below.

Statement type

Delete is DML, Truncate is DDL (What is DDL and DML?)

Commit and Rollback

Variable by vendor

SQL*Server

Truncate can be rolled back.

PostgreSQL

Truncate can be rolled back.

Oracle

Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.

However, see Flashback below.

Space reclamation

Delete does not recover space, Truncate recovers space

Oracle

If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.

Row scope

Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.

Oracle

When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table's data is possible.

Object types

Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)

Data Object Identity

Oracle

Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.

Flashback (Oracle)

Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.

However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition

Use of FLASHBACK in Oracle
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

Privileges

Variable

Oracle

Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.

Redo/Undo

Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.

Indexes

Oracle

A truncate operation renders unusable indexes usable again. Delete does not.

Foreign Keys

A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.

Table Locking

Oracle

Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.

Triggers

DML triggers do not fire on a truncate.

Oracle

DDL triggers are available.

Remote Execution

Oracle

Truncate cannot be issued over a database link.

Identity Columns

SQL*Server

Truncate resets the sequence for IDENTITY column types, delete does not.

Result set

In most implementations, a DELETE statement can return to the client the rows that were deleted.

e.g. in an Oracle PL/SQL subprogram you could:

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;

Truncate or Drop and Create Table

Truncating the table does not leave row-by-row entries in the transaction log - so neither solution will clutter up your logs too much. If it were me, I'd truncate over having to drop and create each time.

Mysql: Which to use when: drop table, truncate table, delete from table

drop table tablename;
- After this, it's gone. No more table. No more data.
- Use this when you don't need that table any more.
truncate table tablename;
- After this, the table is empty, and (importantly) auto-incrementing keys are reset to 1. It's quite literally like having a brand new table.
- Use this when you just want an empty table. It's faster than DELETE because it simply deletes all data. DELETE will scan the table to generate a count of rows that were affected.
delete from tablename;
- This lets you filter which rows to delete based on an optional WHERE clause.
- Use this when you want to delete specific records, eg: DELETE FROM tablename WHERE username = 'joe'

Does TRUNCATE and DELETE produce same results?

FALSE — I'll explain in the context of MySQL's InnoDB engine. You tagged the question both mysql and oracle11g.

DELETE

DELETE can be part of a transaction. The action can be rolled back. It copies old records to the rollback segment, and if you commit then those record versions are garbage-collected. If you rollback, the record versions are restored to the table.
DELETE does not reset the AUTO_INCREMENT counter for the table.
DELETE does not create a new tablespace. The size of the tablespace does not shrink. So it won't reduce the footprint of the table on disk, it will only mark most of the pages in the tablspace as "free", i.e. may be overwritten by subsequent data.
DELETE executes delete triggers for each row affected.
DELETE requires the DELETE privilege.
You can do a DELETE with a JOIN clause, so you can optionally delete from multiple tables in one statement.
If you use binary logs to record the changes and you use binlog_format=ROW, the binary log will fill with as many row images as the number of rows deleted. This could take a lot of space.

TRUNCATE TABLE

TRUNCATE TABLE is a DDL statement, as you said, so it performs an implicit commit.
TRUNCATE TABLE creates a new tablespace with minimum size, and drops the original tablespace.
TRUNCATE TABLE resets the AUTO_INCREMENT for the respective table.
TRUNCATE TABLE does not copy any records to the rollback segment.
TRUNCATE TABLE does not execute any triggers.
TRUNCATE TABLE requires the DROP privilege.
You can only truncate one table per statement.
TRUNCATE TABLE will not take more space in the log for a large table. DDL statements are always written to the binary log in statement format, even if you set binlog_format=ROW.

TRUNCATE TABLE is basically equivalent to this sequence:

  CREATE TABLE new_table LIKE original_table; -- i.e. with zero rows
  RENAME TABLE original_table TO table_to_drop, new_table TO original_table;
  DROP TABLE table_to_drop;

Difference Between Drop Table and Truncate Table