What are the differences between drop a table and truncate
a table?
Answers were Sorted based on User's Feedback
Answer / velmurugan
Drop - will Delete the table date as well as the table
structure.
Truncate - Will Delete the table data only. The table
structure will remains.
Is This Answer Correct ? | 71 Yes | 9 No |
Answer / meena bisht
Drop: drop means it will drop the table including structure
and free the memory from hard disk.
Truncate: Truncate will truncate the data. The structure
will remain there and also will free the memory.
Is This Answer Correct ? | 33 Yes | 4 No |
Answer / soumik
1.DELETE table will result into deletion of data from the
table without affecting the table structure. But since
delete statement is a DML(Data Manipulation Language)
statement, it directly dosnt reflect the database. In other
words, the deleted data will be stored in a separate
tablespace called UNDO tablespace(which takes up memory)
that can be retrieved by using a ROLLBACK statement.
2. DROP table results into loss of data and structure both
which cannot be retrieved. Drop statement is DDL(Data
Definition Language) which is AUTO-COMMIT.
3. TRUNCATE table will clear the data from the table only
without affecting the table structure. Data cannot be
retrieved after executing this statement as well, since it
an AUTO-COMMIT statement. Truncate is also a DDL statement.
Is This Answer Correct ? | 9 Yes | 0 No |
Answer / ramavtar rajput
drop--> means table is delete ,not roll back,
truncate-->
Is This Answer Correct ? | 18 Yes | 11 No |
Answer / ......v......
TRUNCATE <table_name>;
This will only Remove table data and not structure of table
DROP <table_name>;
This will Remove table data and structure both
In both the cases data can be Rollbacked in SQL Server 2000
or 2005.....,But NOT POSSIBLE in other.........!
Is This Answer Correct ? | 10 Yes | 4 No |
Answer / guru
1.DELETE:
- delete space allocated by mysql server and the structure of table remains same.
- used by either 'WHERE' clause or without it in mysql.
- data can be roll backed
2.TRUNCATE:
- Remove rows from mysql table but the structure of table remains same.
- The data cannot be roll backed
- can be used by only without 'WHERE' clause in mysql.
3. DROP:
- Remove whole table from mysql database.
- The data cannot be roll backed
For more details visit: http://www.phponwebsites.com/2014/01/mysql-delete-truncate-drop-difference.html
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / shital
Delete - delete all the data from table but structure still remains as it is.
Truncate - delete all data from table but structure remains and memory still allocated for table.
Drop - delete all data as well as structure and also free the memory.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / raabhu
Drop a Table : Delete over all table is called drop...
Truncate a table : Delete only the field names of table is
called truncate...
Is This Answer Correct ? | 6 Yes | 12 No |
Answer / karthik
truncate means it will truncate the data, we can retrieve
it whenever we want using commit
drop - it delete the full structure as well as the data.
cant make a recovery.
Is This Answer Correct ? | 2 Yes | 10 No |
Answer / sudha
Truncate Table - Truncate table is the combination of Drop
and Create. It [Truncate]can drop and recreate the table.
Delete - Delete will deletes all the data in the table and
structure of the table.
Is This Answer Correct ? | 4 Yes | 20 No |
How to decrement dates by 1 in mysql?
How do I clear the command in mysql?
Is mysql free download?
How can you change the password of a mysql user?
What is mysql community edition?
How do I start mysql from command line?
What is the role of a limit in a mysql query?
What is the latest version of php and mysql?
What is full text indexing in mysql?
How do I zip a file in mysql?
What is mysql in dbms?
What is pdo in mysql?