Click here to Skip to main content
13,899,733 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
What is actual different between truncate and delete
Posted
Updated 20-Jun-16 19:47pm
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

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.
   
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!!!!
manub22 22-Sep-15 2:40am
   
I disagree with your TRUNCATE 7th point "7. Rollback is not possible."

In TRUNCATE statement Rollback is also possible, provided its under TRANSACTION. Same is with DELETE statement.

Check my blog post here on this: http://sqlwithmanoj.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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.
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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.
   
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 11

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.
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190306.1 | Last Updated 28 Dec 2017
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100