Click here to Skip to main content
15,503,850 members
Please Sign up or sign in to vote.
1.00/5 (10 votes)
See more:
What is actual different between truncate and delete
Posted
Updated 17-Aug-22 3:17am
Comments
aaronzeng 28-Dec-17 12:03pm    
TRUNCATE

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.

DELETE

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.

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
 
Share this answer
 
v2
Comments
VJ Reddy 23-Apr-12 1:34am    
Good answer. 5!
Adarsh chauhan 31-Jul-13 1:29am    
Nice and to the point explanation.. +5
parul77 23-Apr-12 1:36am    
thank you reddy
♥…ЯҠ…♥ 10-Apr-14 9:16am    
Eventhough its copy paste from http://www.dotnetfunda.com/forums/show/9321/what-is-the-difference-between-delete-and-truncate-in-sql-server, I cant vote up there so am doing here for the OP
anil.singh581 10-Oct-14 2:26am    
Yes!!!!
Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here...

A very quick search gave MSDN: http://msdn.microsoft.com/en-us/library/ms177570.aspx[^] - which explains in great detail!

In future, please try to do at least basic research yourself, and not waste your time or ours.
 
Share this answer
 
DELETE :

Removes your record may be DELETE TABLE,ROW etc.

Truncate :

It will chop the excess byte.

for ex . any string you gave size as 10
and if input is more than 10 character rest will truncated
 
Share this answer
 
If you have an identity column in your table delete will keep a record of this and when you insert will continue from the last id.

Truncate will clear this and start again from 1 or whatever you seed is set to.
 
Share this answer
 
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900