SQL Server TRUNCATE and DELETE

TRUNCATE : TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement. TRUNCATE is a DDL command.

Syntax : TRUNCATE Table Table_name

EXAMPLE :

TRUNCATE TABLE CUSTOMERS;

Restrictions : You cannot use TRUNCATE TABLE on tables that

·         Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

·         Participate in an indexed view.

·         Are published by using transnational replication or merge replication.

DELETE : Removes one or more rows from a table or view in SQL Server.

Syntax : DELETE FROM table WHERE conditions;

EXAMPLE :

USING ONE CONDITION

DELETE FROM employees WHERE first_name = 'Sunil';

USING TWO CONDITIONS

DELETE FROM employees WHERE last_name = 'Johnson'

AND employee_id >= 80;

USING TOP KEYWORD

DELETE TOP(3)FROM employees WHERE last_name = 'Sunil'

Difference between TRUNCATE and DELETE :

TRUNCATE	DELETE
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
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.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
Truncate cannot be used with indexed views	Delete can be used with indexed views