SQL Truncate, Delete, Drop advise

Question

I have a table in a SQL db that I want to remove the data from? I want to keep the columns though.

e.g. my table has 3 columns, Name, Age, Date. I don't want to remove these, i just want to remove the data.

Should I should Truncate, Delete or Drop?

Answer 1

delete from TableName should do the trick

DROPing the table will remove the colums too.

Answer 2

Delete: The DELETE Statement is used to delete rows from a table.

Truncate: The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

*Drop:* The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database

Answer 3

We can rollback the data in conditions of Delete, Truncate & Drop. But must be used Begin Transaction before executing query Delete, Drop & Truncate.

Here is example :

Create Database Ankit

Create Table Tbl_Ankit(Name varchar(11))

insert into tbl_ankit(name) values('ankit');
insert into tbl_ankit(name) values('ankur');
insert into tbl_ankit(name) values('arti');

Select * From Tbl_Ankit

/*======================For Delete==================*/
Begin Transaction
Delete From Tbl_Ankit where Name='ankit'

Rollback
Select * From Tbl_Ankit

/*======================For Truncate==================*/
Begin Transaction
Truncate Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

/*======================For Drop==================*/
Begin Transaction
Drop Table Tbl_Ankit 

Rollback
Select * From Tbl_Ankit

Answer 4

Truncate the table. That would be good option in your case

Answer 5

Truncate is very fast - like quick format of the table. It does not require any extra space when deleting. You can not rollback his operation. You can not specify conditions. This is best choice for deleting all data from table.

Delete is much slower and you need extra space for this operation, because you must be able to rollback the data. If you need to delete all data, use truncate. If you need to specify conditions, use delete.

Drop table - you can delete data from table by dropping and creating it again like you would truncate it, but it is slower and you can have some other problems, like foreign key dependencies. I definitely don't recommend this operation.

Answer 6

Don't drop - it will delete the data and the definition.
If you delete - the data is gone and auto-increment values go on from the last value.
If you truncate - then it is like you just did create the table. No data and all counters resetted