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

Question

What's the difference between truncate, drop and delete 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 1

TRUNCATE

1. The TRUNCATE statement removes all data from a table but leaves the table structure intact.

   e.g. TRUNCATE TABLE my_table;

2. This statement is much faster than using the DELETE statement to remove data from the table because it doesn't log each row deletion, and it does not use as much storage space.

3. However, it cannot be undone, and it requires the user to have the DROP privilege.

DELETE

1. The DELETE statement is used to remove rows from a table one by one.

   e.g. DELETE FROM my_table WHERE id = 123;

2. This statement is slower than TRUNCATE, but it's more flexible.

3. You can use it to delete specific rows based on criteria, or you can delete all rows in a table if you don't include a WHERE clause.

4. It also allows you to roll back the changes if needed.

DROP

1. The DROP statement deletes the entire table, including all data and the table structure.

   e.g. DROP TABLE my_table;

2. This statement is the most destructive, and it cannot be undone.

3. It's useful if you need to completely remove a table and its data from a database.

4. However, you should be very careful when using this statement, as it permanently removes all data in the table.

Answer 2

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