What's the Difference Between Truncate and Delete in Sql

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;

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;

Pros & Cons of TRUNCATE vs DELETE FROM

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.

EDIT:
Note that the above is incorrect for SQL Server (but it does apply to Oracle). In SQL Server, it is possible to rollback a truncate operation if you are inside a transaction and the transaction has not been committed. From a SQL Server perspective, one key difference between DELETE FROM and TRUNCATE is this:
"The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log."

In other words, there is less logging during a TRUNCATE because only the page deallocations are recorded in the transaction log, whereas with a DELETE FROM each row deletion is recorded. That's one of the reasons TRUNCATE is lightning fast.

Note also from that MSDN link that you cannot truncate tables that are referenced by foreign key constraints, participate in an indexed view, or are published by using transactional replication or merge replication.

EDIT 2:
Another key point is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.
Reference: Ben Robinson's answer.

What is the difference between truncate and delete?

TRUNCATE is executed using a table lock and whole table is locked for remove all records.
DELETE is executed using a row lock, each row in the table is locked for deletion.
TRUNCATE removes all rows from a table mean to say we cannot use where condition for truncate
The DELETE command is used to remove rows from a table based on WHERE condition.

Difference between TRUNCATE and DELETE?

When we are using Truncate, we are de-allocating the whole space allocated by the data without saving into the undo-table-space. But, in case of Delete, we are putting all the data into undo table-space and then we are deleting all the data.
The main points that put TRUNCATE in the DDL camp on Oracle, are:

TRUNCATE can change storage parameters (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.
TRUNCATE does an implicit commit, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does

comparison of truncate vs delete in mysql/sqlserver

DELETE

DELETE is a DML Command.
DELETE statement is executed using a row lock, each row in the table is locked for deletion.
We can specify filters in where clause
It deletes specified data if where condition exists.
Delete activates a trigger because the operation are logged individually.
Slower than truncate because, it keeps logs.
Rollback is possible.

TRUNCATE

TRUNCATE is a DDL command.
TRUNCATE TABLE always locks the table and page but not each row.
Cannot use Where Condition.
It Removes all the data.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
Faster in performance wise, because it doesn't keep any logs.
Rollback is possible.

DELETE and TRUNCATE both can be rolled back when used with
TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
if there is a PK with auto increment, truncate will
reset the counter

http://beginner-sql-tutorial.com/sql-delete-statement.htm

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

Difference between Delete and Truncate in sql server. Was I wrong...?

Apparently the idea that truncate can't be rolled back is a myth.

Summary

Truncate can be rolled back from within a transaction. The difference is that the truncate gets logged as a page deallocation in the log instead of a delete for each record being removed. Once the operation is committed it can't be undone.
When you truncate, you are resetting the identity field. When you delete, you are not.
You can't truncate a table that is referenced by a foreign key.
Truncating will not fire any ON DELETE triggers

What's the Difference Between Truncate and Delete in Sql