Getting Started with Adobe After Effects - Part 6: Motion Blur
This module helps you to share, discuss and learn interview questions and answers of different technologies

Difference between DELETE & TRUNCATE

Oct 27 2011 6:26AM by Robert Dennyson   

Question

What is the different between delete and truncate?

Answer

Most of the interviewer will be satisfied with this answer.

  • TRUNCATE is a DDL command and DELETE is a DML command
  • Delete performs row by row, where as TRUNCATE performs at page level

If the interviewer needs more elaborative answer(Not frequently)

Truncate

  • TRUNCATE is a DDL command
  • TRUNCATE TABLE always locks the table and page but not each row
  • Cannot use Where Condition
  • It Removes all the data
  • TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
  • Faster in performance wise, because it doesn’t keep any logs
  • Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table
  • TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo
  • If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
  • Restrictions on using Truncate Statement
    1. Are referenced by a FOREIGN KEY constraint.
    2. Participate in an indexed view.
    3. Are published by using transactional replication or merge replication.


Delete

  • DELETE is a DML command
  • DELETE statement is executed using a row lock, each row in the table is locked for deletion
  • We can specify filters in where clause
  • It deletes specified data if where condition exists.
  • Delete activates a trigger because the operation are logged individually.
  • Slower than truncate because, it keeps logs
  • Rollback is possible
  • keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
  • The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
  • DELETE retain the identity
  • Delete works at row level, thus row level constrains apply

Read More..   [28 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  · 


Robert Dennyson
11 · 14% · 4420
0
Liked
 
0
Asked



Submit

Your Comment


Sign Up or Login to post a comment.

"Difference between DELETE & TRUNCATE" rated 5 out of 5 by 2 readers
Difference between DELETE & TRUNCATE , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]