How to evaluate which command to use between truncate/delete

Asked

Viewed 219 times

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 table by cleaning the same for a next use.

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

I don’t understand what you mean by that watermark.

My question is, how to evaluate and when/which command to use? And which one already reset fields identity

sql sql-server

  • if you’re going to delete the whole table, use truncate, otherwise delete, I don’t think any will reset the sequence of pk

  • 1

    @Igorcarreiro: Watermark, or high water mark, is a concept used in the Oracle Database.

  • 2

    Possible duplicate of What difference between TRUNCATE and DELETE + CHECKIDENT?

  • 1

    The questions are quite similar. I believe they differ because the AP here asks which Zera the columns identity.

  • @vnbrs is right

1 answer

5


It is always good to use the official or most reliable documentation possible.

The TRUNCATE removes all rows from a table. It does not generate single line deletion logs. This is a faster way, since it does not generate transaction logs. If the table contains an idenity column, your Seed will be reset. It is worth noting that it is a command of definition data, DDL.

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

By not applying a condition to the DELETE it will show itself slower than the TRUNCATE. The DELETE line by line deleting values and TRUNCATE nay.

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

  • 1

    Only as a complement: TRUNCATE cannot be used in some situations, as stated in the documentation. One of these situations is when the table contains column that is referenced by restriction present in another table (FOREIGN KEY).

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.