Difference between delete and truncate

Question

1.00/5 (10 votes)

See more:

SQL

What is actual different between truncate and delete

Posted 21-Apr-12 1:26am

Pradhan Gagan

Updated 17-Aug-22 2:17am

Add a Solution

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.

6 solutions

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.

Posted 21-Apr-12 1:31am

OriginalGriff

Solution 1

Refer these:

http://codebetter.com/raymondlewallen/2005/05/09/the-difference-in-truncate-and-delete-in-sql-server/[^]

http://www.technoreader.com/Difference-between-Truncate-and-Delete-in-SQL.aspx[^]

Posted 21-Apr-12 1:31am

sravani.v

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

Posted 21-Apr-12 1:40am

ersumitsahu

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.

Posted 22-Apr-12 14:00pm

milenalukic

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.

Posted 16-Jun-14 17:09pm

Vikas Nalawade

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

parul77 · Accepted Answer · 2012-04-22T19:17:00

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.