What is the difference between DELETE and TRUNCATE statement in SQL?Ask Questions

 Posted on 03/08/2018 7:23:05 AM

                                                                                                                                                                                      
Share On: facebook gplus twitter
profile
Asked by Hardik Parekh on 03/08/2018 7:23:05 AM Score: 238 points
Add Comment:
Submit

Comments

1 Answer

0 Corrected Answers
3
Profile
Answered by Shailesh Chaudhary on Aug 3 2018 8:23AM Score: 681 points
TRUNCATE
  • TRUNCATE is a DDL command
  • TRUNCATE is executed using a table lock and whole table is locked for remove all records.
  • We cannot use Where clause with TRUNCATE.
  • TRUNCATE removes all rows from a table.
  • Minimal logging in transaction log, so it is performance wise faster.
  • 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 log.
  • Identify column is reset to its seed value if table contains any identity column.
  • To use Truncate on a table you need at least ALTER permission on the table.
  • Truncate uses the less transaction space than Delete statement.
  • Truncate cannot be used with indexed views.
DELETE
  • DELETE is a DML command.
  • DELETE is executed using a row lock, each row in the table is locked for deletion.
  • We can use where clause with DELETE to filter & delete specific records.
  • The DELETE command is used to remove rows from a table based on WHERE condition.
  • It maintain the log, so it slower than TRUNCATE.
  • The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
  • Identity of column keep DELETE retain the identity.
  • To use Delete you need DELETE permission on the table.
  • Delete uses the more transaction space than Truncate statement.
  • Delete can be used with indexed views.
Add Comment:

Post Your Answer

  •  
  •  

Existing Members

Sign in to your account
Email Address
Password
...or Join us
Download, Vote, Comment, Publish.
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.