comparison of truncate vs delete in mysql/sqlserver [duplicate]

by

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 possible.

  • DELETE and TRUNCATE both can be rolled back when used with
    TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
  • if there is a PK with auto increment, truncate will
    reset the counter

http://beginner-sql-tutorial.com/sql-delete-statement.htm

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. sql query to modify the selected value from database [closed]
  3. Calculate Average of the largest values in multiple columns
  4. SQL Print the name of all Employees together with the name of their supervisor
  5. Storing JSON in database vs. having a new column for each key
  6. SQL: Repeat a result row multiple times, and number the rows
  7. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  8. Why do results from a SQL query not come back in the order I expect?
  9. When to use SELECT … FOR UPDATE?
  10. Best practices for SQL varchar column length [closed]
  11. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  12. how to cast the hexadecimal to varchar(datetime)?
  13. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  14. Why in SQL NULL can’t match with NULL?
  15. Convert SQL Server query to MySQL [duplicate]
  16. MySQL offset infinite rows
  17. Ordering by the order of values in a SQL IN() clause
  18. SQL Selecting from two Tables with inner join and limit [duplicate]
  19. MySQL Group by SUM
  20. Get the latest date from grouped MySQL data
  21. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  22. MySQL Error 1264: out of range value for column
  23. Special Characters in MySQL Table Name
  24. MySQL Aggregate Functions without GROUP BY clause
  25. Is it possible to reference one column as multiple foreign keys?
  26. mysql equivalent data types
  27. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  28. Empty IN clause parameter list in MySQL
  29. creating a random number using MYSQL
  30. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]

Leave a Comment