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 ·  ·  ·  ·