DELETE vs TRUNCATE

What are the differences between DELETE and TRUNCATE [DELETE vs TRUNCATE]?

	DELETE	TRUNCATE
Use	DELETE is used to remove the data from the table. DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted. so You can use conditions (WHERE clause) in DELETE.	TRUNCATE is also used to remove the data from the table. TRUNCATE is also a logged operation, but in a different way, This means TRUNCATE logs the de-allocation of the data pages in which the data exists. The de-allocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse, so You can’t use conditions (WHERE clause) in TRUNCATE.
SPEED	DELETE is slower than TRUNCATE. Reason: When you type DELETE, all the data get copied into the Rollback Tablespace first, then delete operation get performed. Thatswhy when you type ROLLBACK after deleting a table, you can get back the data (The system gets it for you from the Rollback Tablespace). All this process takes time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace.	TRUNCATE is much faster than DELETE. Reason: When you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace.
ROLLBACK DATA	Once you DELETE, you can get back the data using Rollback.	Once you TRUNCATE, you can’t get back the data. TRUNCATE removes the record permanently.
TRIGGER FIRING	Trigger gets fired when DELETE.	Trigger doesn't get fired when TRUNCATE.
RESET IDENTITY	DELETE will not reset any identity columns to the default seed value.	TRUNCATE will reset any identity columns to the default seed value.
EXAMPLE	CREATE TABLE Parent (RecordId int IDENTITY) GO INSERT Parent DEFAULT VALUES; GO 5 BEGIN TRAN DELETE Parent WHERE RecordId = 3 ROLLBACK SELECT * FROM PARENT RESULT: RecordId 1 2 3 4 5	CREATE TABLE Parent(RecordId int IDENTITY) GO INSERT Parent DEFAULT VALUES; GO 5 TRUNCATE TABLE Parent SELECT * FROM PARENT RESULT: RecordId

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF