Truncate vs Delete in MSSQL .


Posted On: Feb 22, 2018


Delete in MSSQL

  • Deleting is one the most expensive operation, it's logged. Every row deleted is logged to the transaction log.
  • Delete from ... allows to you delete specific rows.
  • Delete from ... marks the row as a ghost row.
  • Delete from ... does not release the space of the deleted rows, you will need to run DBCC SHRINKFILE to recover the space [single user mode].

Truncate in MSSQL

  • Often people want to empty a temporary table to repopulate it with current data, then it's much faster to use the truncate statement instead of the delete statement.
  • truncate table is the most efficient way of emptying a table.
  • It's not possible to use the truncate table when you only want to delete some rows based on a condition.
  • Truncate table does not delete rows, it deallocates the data pages and release the space.
  • Truncate table cannot be used when the table is referenced by a foreign key or involved in data replication.
  • Truncate table is recommended for temporary tables that need to be cleared for the next process.

    Related Questions

    Please Login or Register to leave a response.

    Related Questions

    Mssql Interview Questions

    What is MSSQL?

    MSSQL stands for Microsoft Server SQL that is a Microsoft’s relational database management system. It is a featured d..

    Mssql Interview Questions

    What is SQL server agent and what are the two modes of authentication in SQL Server?

    SQL Server agent The SQL Server agent plays an important part in the day to day tasks of the SQL Server Database Admini..

    Mssql Interview Questions

    What is SQL Profiler?

    It is a tool that allows system’s administrator to monitor the events in SQL Server.It is mainly used to capture and ..