cybork cybork - 1 year ago 79
SQL Question

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

What's the difference between

of tables? And when to choose for which? Does anyone has a quick comparison? I've seen a lot of info about this, but haven't found it in a clear overview yet. I hope this post helps in the understanding.

I mean like being used in these statements in t-sql:

truncate table TableX
drop table TableX
delete table_name

Answer Source

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: 

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 table tableX
alter columnX datatypeX


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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download