Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.


YOU ARE HERE: HOME Questions What is the difference between Truncate and Delete in SQL Server

Difference between Delete and Truncate in SQL Server

View(s): 23234

What is the difference between Truncate and Delete in SQL Server

Answer 1)

1. When we use DELETE, SQL Server will maintain the transaction log. So that, it can restore the state (rows in a table) when user requested rollback.

Where as in case of truncate, SQL Server TRUNCATE logs the de-allocation of the data pages in which the data exists.  The de-allocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

2. In DELETE command we can use WHERE clause, where as in TRUNCATE command, we cannot use any clauses.

3. You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the constraints, TRUNCATE the table, and re-apply the constraints.

4. TRUNCATE will reset any identity columns to the default seed value.

5. DELETE causes triggers to fire where as TRUNCATE will not

  Asked in:  TCS (Tata Consultancy Services)   Expertise Level:  Intermediate
  Last updated on Tuesday, 06 December 2011
4/5 stars (17 vote(s))

Register Login Ask Us Write to Us Help