What's the difference between
truncate table TableX
drop table TableX
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
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 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