DELETE, TRUNCATE and DROP @ SQL Server

TRUNCATE TABLE:

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

       Advantages of TRUNCATE TABLE :

1.              Less transaction log space is used.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 data and records only the page deallocations in the transaction log.
2.              Fewer locks are typically used. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
3.             Without exception, zero pages are left in the table.  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

           Restrictions of TRUNCATE TABLE:

1. TRUNCATE statements do not fire triggers.
   
2. Records removed by the TRUNCATE TABLE statement cannot be restored.
   
3. You can not specify a WHERE clause to narrow down the rows.
   
4. you can not  retain the identity counter.
5. You cannot specify a WHERE clause in a TRUNCATE TABLE statement. It is all or nothing.
   
6. You can not usee TRUNCATE TABLE when:

•                        Are referenced by a FOREIGN KEY constraint.
  
•                       Participate in an indexed view.
  
•                       Are published by using transactional replication or merge replication.

DELETE TABLE: 

• DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as            maintaining log sequence number (LSN) information.  
  
• You can also specify a WHERE clause to narrow down the rows to be deleted.
• When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the            empty pages requiring manual release using DBCC SHRINKDATABASE (db_name). 
•             If you need to retain the IDENTITY counter then se the DELETE statement.
• When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following         statements can be used to achieve the same result as TRUNCATE TABLE:

DELETE from "table_name" 

DBCC CHECKIDENT("table_name", RESEED, "reseed_value")

DROP TABLE:

 To remove the table definition in addition to its data, use the DROP TABLE statement.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.