Wednesday, 19 August 2015

What is difference between the Delete and Truncate in Sql server

Delete :
  • Delete can delete all rows or particular rows .
  • Delete is the DML Command. (DML Stands for Data manipulation language)
  • Delete is recorded in log file.
  • Data can be restored.
  • Delete will not reset Identity.
   Syntax: delete from <tablename> [where <cond>]

  •   delete * from emp . (delete all the rows from emp table)
  •   delete * from emp where job in ('clerk','manager')  (Delete the employee record working as      clerk or manager).  
Truncate :
  •  Truncate command is used to release memory allocated for the table.
  •  Truncate can delete  only all rows.
  •  Truncate is not recorded in log file.
  • Data can't be restored.
  • Truncate will reset Identity.
  • truncate is faster than delete
  •      truncate table<tablename>.
Example :
  •   Truncate table emp       

No comments:

Post a Comment