Drop is delete the table and strecture too..
TRUNCATE TABLE is functionally identical to DELETE
statement with no WHERE clause: both remove all rows in the
table. But TRUNCATE TABLE is faster and uses fewer system
and transaction log resources than DELETE.
The DELETE statement removes rows one at a time and records
an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data
pages used to store the table's data, and only the page
deallocations are recorded in the transaction log.
TRUNCATE TABLE removes all rows from a table, but the table
structure and its columns, constraints, indexes and so on
remain. The counter used by an identity for new rows is
reset to the seed for the column. If you want to retain the
identity counter, use DELETE instead. If you want to remove
table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint; instead, use DELETE statement
without a WHERE clause. Because TRUNCATE TABLE is not
logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in
an indexed view.
Delete is used to delete the all or some rows in a table with where condition Delete is Data Manuplation Language command(DML).
Truncate deletes the all the rows at one go you cannot use where condition in it and it is Data Definition Language command(DDL).
..Delete the structure
...can be rollback
...does not delete the identity of table
...can't be rollback
...delete the identity of table
Difference b/w Truncate and Delete
1) Truncate is Data Dafanition Language.
Delete is Data Manipulation Language.
2) Truncate command cannot be rollback
Since DELETE IS DML, Delete command can be rollback.
3) Truncate is faster than Delete.
Delete is slower than Truncate.
4) WHERE clause can be used with DELETE
and not with TRUNCATE.
5) When Table is Truncated memory occupied is released and also water mark is adjusted.
When Table is deleted memory occupied is not released and also water mark is not adjusted
DROP Will remove a table from DB
DELETE logs each row deletion, and TRUNCATE TABLE logs only the de-allocation of whole data pages.
Truncate table does not cause DELETE triggers to fire.
Truncate table will not work if the table is referenced in a foreign key constraint irrespective of the dependent table containing the keys or not.
Delete statement will raise error, if the table is referenced in a foreign key constraint and there are depending rows in the child table on the keys.
Truncate table resets the identity value to 1, delete does not reset the identity value.
Drop Table Statement is Used to Delete the Table from DB.
Delete Statement : Is Used to Delete the Record from a particular Table in this a log is maintained. means we can recover the data.
Truncate Table: No Log is Maintained and the structure of the table is dropped in this
Lakhan Pal Garg
Please rate this answer if it helped you.
Thanks & Regards
Lakhan Pal Garg
syntax:drop table <table name>
delete:delete is used to delete the record from table
it can be used with where clause.it is slow when compared to truncate command.identity option is not resetted in case of delete command
syntax:1.delete from <table name> --delete all the rows
2. delete from <table name> where <condition>
truncate:used to delete all the records from the table.it cannot be used with where clause.identity option is resetted in case of truncate command
syntax:truncate table <table name> --deletes all the rows