How to evaluate which command to use between truncate / delete

Question

How to evaluate which command to use between truncate / delete

Navigation

#1 by (5 votes)

3

I was reading about these commands Truncate table and Delete from , where I saw this explanation

Truncate table - This command removes the watermark from the wiping table   the same for next use.

Delete from - But this command does not remove the watermark from the table   keeping it the same size if it is a delete full.

I do not understand what this watermark means.

My question is, how to evaluate and when / which command to use? And which of them have already set fields identity

sql sql-server

asked by anonymous 26.09.2017 / 21:54

1 answer

CreateDatabase Delphi VCL How to replicate master upgrade to branches in Team Foundation Server?

score 5 · Accepted Answer

It's always a good idea to go to the official or most reliable documentation.

TRUNCATE removes all lines of a table. It does not generate row exclusion logs individually. This is a faster way, since it does not generate transaction logs. If the table contains an idenity column, its seed will be zeroed. It is worth mentioning that it is a definition command, DDL.

DELETE removes lines from a table based on an imposed condition. If you have not imposed a condition, you will delete all rows from the table. Here is a data manipulation command, DML.

Failing to apply a condition to DELETE will be slower than TRUNCATE . The DELETE passes line to line deleting values and TRUNCATE does not.

If the purpose is to delete all lines, use TRUNCATE . It is faster, consumes less resources and was made for this. Microsoft defines this as a good practice here >.