TRUNCATE TABLE:
TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1. DELETE will not do this.
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause,however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources. TRUNCATE TABLE empties it, but leaves its structure for future data.
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.
Syntax for Truncate is
TRUNCATE TABLE table_name
Ex: TRUNCATE table Roja
DROP TABLE :
Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. DROP TABLE deletes the table.Drop will delete all the existing structure. if it applies to a table, Truncate will delete only the data while keeping the structure of the table as it is.
Syntax for the DROP is
DROP TABLE table_name
Ex: DROP table Roja