Sunday, 14 October 2018

Difference between SQL Server Delete,Truncate and Drop Commands

What are the Key differences between Delete,Truncate and Drop Commands
TRUNCATE is a DDL command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.
We cannot use Where clause with TRUNCATE.
TRUNCATE removes all rows from a table.
Minimal logging in transaction log, so it is performance wise faster.
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.
Identify column is reset to its seed value if table contains any identity column.
To use Truncate on a table you need at least ALTER permission on the table.
Truncate uses the less transaction space than Delete statement.

Truncate cannot be used with indexed views. 
Notes :
The Truncate will Fail when there is Foreign key violation, and will get the below error.
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
To avoid this error, we can set the below properties.

TRUNCATE Table $table_name; 
--Set back the Check again to default.

DELETE is a DML command.
DELETE is executed using a row lock, each row in the table is locked for deletion.
We can use where clause with DELETE to filter & delete specific records.
The DELETE command is used to remove rows from a table based on WHERE condition.
It maintain the log, so it slower than TRUNCATE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Identity of column keep DELETE retain the identity.
To use Delete you need DELETE permission on the table.
Delete uses the more transaction space than Truncate statement.
Delete can be used with indexed views.

The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired.

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back (undone) easily, while DROP and TRUNCATE 

operations also can be rolled back if we define them in a TRANSACTION.

Note :
We can rollback DELETE, TRUNCATE and DROP Operations when we defined them in a TRANSACTION with Save Points.

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts