What is the difference between TRUNCATE and DROP statements?

Question

Why they are used??

Answer 1

TRUNCATE will remove all rows from a table without effecting the table struture so structure is remain as it is
it is similar as delete but in delete we have to take use of where clause
DROP table will delete the table fully with struture

Answer 2

http://www.c-sharpcorner.com/UploadFile/65fc13/difference-between-delete-truncate-and-drop-statements-of-m/

Answer 3

The TRUNCATE statement is used to delete all rows from a given table. This also frees up the space used by the deleted rows unlike DELETE FROM statement.

DROP statement deletes the complete table including the table schema. That means, once dropped, you can not insert rows into the table until you re-create the table using CREATE TABLE. If you truncate the rows, you can again insert into the table without re-creating the table.

BTW just a suggestion, I just googled out the answer for you :-). Just try to use google before posting.

Answer 4

Truncate removeu all dates from a table.-Clean
Drop remove all struct from table- Destroy

Answer 5

Truncate - It means delete data and resets id
Drop - Delete all the entries

Answer 6

truncate removes data from the table ... it empties the table .. but table structure will remain ... so that data can be inserted again to it, if needed

drop table will delete all data as well as table itself. now if we have similar data to insert into the table we cannot unless we create a new table

i recall an incident something like there was a procedure in our company database that used to run on month-end basis in which statements were like
DROP TABLE XYZ
CREATE TABLE XYZ....

after execution new data will be contained in XYZ table

now what happened access to this XYZ table in our schema was requested by some another user for their daily new report ... which was given but in the month end when our monthly procedure ran he lost access to XYZ table ...

then it was understood that by drop table privilliges were also deleted and by simply creating new table with same name will be an entirely new table .

then the monthly procedure was modified to
TRUNCATE TABLE XYZ
INSERT INTO XYZ...

Answer 7

TRUNCATE TABLE customers
DELETE * FROM cutomers

have the same result, delete all rows from the table cutomers. but in this case it is better to use TRUNCATE because it is faster than DELETE *
the * mean : all

after a TRUNCATE or a Delete, the table customers is empty but the stucture is still existing and you can use insert into or select from it.

DROP TABLE customers
will delete the table from the database and if you use a
select * from customers
you will get an error msg (no object cutomers in the database )

Answer 8

truncate pemanately delete data and it it is don't recover by flashback or rollbacl
but in delete data will not permanately delete u can got data by using rollback or flashback keyword
delete it is work like a windows7 recyclebin

Answer 9

truncate -delete all data from table. dont calculate statistic. dont write log.
drop- delete table's own.
delete - delete data from table. calculate statistic,index. write log

Answer 10

Hope this article help you 💚
-http://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1
-- http://beginner-sql-tutorial.com/sql-delete-statement.htm

Answer 11

Truncate will delete only the records entered in a table , with the schema existing even after execution of the command. Drop deletes both schema and records.

Answer 12

Truncate will remove all the rows from the table but table structure remains there.
While drop removes the whole table along with structure. You cannot find your table after dropping it.
Additional- truncate is just like delete command used without any where clause.

Answer 13

TRUNCATE deletes all table rows
DROP deletes entire Table

Answer 14

DROP is the operation which just removes the table from the database and in future if we tried to visualize that table we can't find.

TRUNCATE is the operation which removes only the data of the table but the schema of table just remains in database in future if we try to visualize the structure can be seen using DESC<tablename>

Answer 15

soy nuevo, pero supongo que debe existir diferencia, por tu comentario con tonalidad de desconocimiento e indignación

Answer 16

@Taleb you are wrong

DELETE * from table is wrong syntax

it's only DELETE from table

if you want delete only specific number of rows than use LIMIT if you dont use limit you will delete all rows

example with limit:

DELETE from table LIMIT 100

and there is difference between TRUNCATE and DELETE

TRUNCATE will reset all columns with auto_increment if last id have 30 after truncate next insert will create record with id = 1

if you will use DELETE next record id = 31

Answer 17

if you DROP a table , it no long exist ...If you DELETE a row or multiple rows they no longer exist .Finally if you truncate a table all rows no longer exist

Answer 18

Truncate removes part of the table but drop removes and deletes a table permanently.

Answer 19

truncate is like a reset, drop a delete

Answer 20

For the spanish people TRUNCATE remueve las filas de la tabla sin afectar la tabla pertinente (o sea quedando la estructura de la misma igual), en cambio la cláusula DROP es diferente elimina la tabla con su estructura. I really hope the forum be in other languages.