Delete or Truncate if no conditions involved-CodePudding

I am peer reviewing a code.

I found lots of Delete statement without conditions where Developers are removing data from table and inserting fresh data.

public void deleteAll() throws Exception {
        String sql = "DELETE FROM ERP_FND_USER";
        entityManager.createNativeQuery(sql, FndUserFromErp.class).executeUpdate();
        LOG.log(Level.INFO, "TERP_FND_USER all data deleted");
    }

Shall i make it standard to always use Truncate when delete all data as Truncate is more efficient when delete all? (or shall i be suspicious that in future a condition will come and we would need to change statement)?

I think rollbacking thing also not implemented in code i.e not transactional .

CodePudding user response：

Truncating a table means we have no way of recovering the data, once done.

I believe DELETE is a better option in this case, given that we are expecting the table size is not very big.

If we are expecting a table size to be very big in terms of volume of data we are planning to store, then even in that case I recommend to use to DELETE given that we do not want to delete tables without any conditions in such cases.

Also if we are using a table only for the session of the java program I believe we can use a TEMP table instead of main table, that will help you to not DELETE it explicitly and it will be purged once the session is over.

Truncate should only be used when you are absolutely sure of DELETING the entire table and you have no intention of recovering it at all.

CodePudding user response：

There is no strict answer. There are several differences between DELETE and TRUNCATE commands. In general, TRUNCATE works faster - the reason is evident: it is unconditional and does not perform search on the table. Another difference is the identity: TRUNCATE reseeds the table identity, DELETE does not. For example, you have the users table with column ID defined as identity and column Name: 1 | John Doe 2 | Max Mustermann 3 | Israel Israeli

Suppose you delete user with ID=3 via the DELETE command (with WHERE clause or not - does not even matter). Inserting another user will NEVER create a user with ID=3, most probably the created ID will be 4 (but there are situations when it can be different). Truncating the table will start the identity from 1.

If you do not worry about identity and there are no foreign keys which may prevent you from deleting records - I would use TRUNCATE.

Update: Dinesh (below) is right, TRUNCATE is irreversible. This should be also taken into consideration.

CodePudding user response：

You should use TRUNCATE if you need to reset AUTO_INCREMENT fields.

DELETE of all rows will not.

Other difference is performance, TRUNCATE will be faster than DELETE all row.

Either TRUNCATE or DELETE will remove definitively rows, contrary to what was mentioned in another answer