How does DELETE differ from TRUNCATE? How would you reset a table?

DELETE and TRUNCATE are two ways to remove data from a table in a database, but they differ in behavior and characteristics:

• DELETE is a DML statement that deletes rows based on a condition (or all rows if no condition is specified). It logs each deletion, can be rolled back in a transaction, triggers can be invoked, and deletion occurs row by row.

• TRUNCATE is a DDL statement that quickly removes all rows from a table, usually resetting data without logging each row. It does not invoke triggers, may not be rollbackable in some DBMSs, and is faster than a conditionless DELETE.

If you need to "reset" a table (delete all data), then:

• If rollback capability and triggers are important, use DELETE FROM table;
• If maximum speed is required and rollback is not necessary, use TRUNCATE TABLE table;

Example in Go using database/sql:

_, err := db.Exec("TRUNCATE TABLE users")
if err != nil {
    // handle error
}

Or with DELETE:

_, err := db.Exec("DELETE FROM users")
if err != nil {
    // handle error
}