Interviews - C# Corner

Rohit Kumar
What is the difference between Truncate and Delete command in SQL SERVER?
By Rohit Kumar in SQL Server on Dec 02, 2012
  • kiran kumar
    Feb, 2013 4

    Delete command delete single row or multiple rows are all rows and these rows can be rolled back(we can un delete) Truncate command is used to delete all the rows in table except table structure(the rows deleted using truncate command cannot be un deleted). This is an autocommit command

    • 1
  • beauXjames tschirhart
    Jan, 2013 8

    As they both remove rows from a table, Truncate will also 'reset' any identity seeds as though none have been set. I'd recommend you only use Truncate when running tests before production unless you really know what you're wanting to do. Also, Truncate is usually only available as a T-SQL command and not something you can access via EF or LINQ or any other method besides inline string SQL...

    • 1
  • vipul handa
    Dec, 2012 26

    The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

    • 1
  • vipul handa
    Dec, 2012 26

    TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

    • 1
  • Niks Bisht
    Mar, 2013 11

    1-we can use where clause in delete but can't do in truncate. 2-we can use trigger in delete but can't do in truncate. 3-delete is slower than truncate 4-delete makes log files while truncate can't do that so truncate is much faster than delete. 5- we can use delete in triggers but not in truncate.

    • 0
  • Jaya Kukudkar
    Feb, 2013 24

    Truncate reset identity while delete do not reset identity

    • 0
  • sandy
    Jan, 2013 28

    in truncate there in no possible for individual record deletion we can delete all records at a time but by using delete possible

    • 0
  • sarath kumar
    Dec, 2012 15

    Delete Never removes Identity, But Truncate removes identity. If the Foreign Key represented means delete possible. But Truncate not Possible when it has the references...

    • 0
  • swayanajyoti pati
    Dec, 2012 13

    1.delete command support where clause and it can delete only specific rows or all rows from the table. -Truncate command does not support where clause and it can only delete all rows from the table. 2.delete command deletes rows one by on -Truncate command deletes rows page by page

    • 0

Unlimited Flat-Free License

Most Popular Companies

Most Popular Job Functions