{"id":304,"date":"2014-10-27T09:17:03","date_gmt":"2014-10-27T08:17:03","guid":{"rendered":"http:\/\/www.codereview.co\/?p=304"},"modified":"2014-10-24T09:23:15","modified_gmt":"2014-10-24T08:23:15","slug":"what-is-the-difference-between-the-delete-and-truncate","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/what-is-the-difference-between-the-delete-and-truncate\/","title":{"rendered":"What is the difference between the DELETE and TRUNCATE"},"content":{"rendered":"

Solution<\/strong><\/span><\/p>\n

The delete command removes the rows from a table on the basis of the condition that we provide a WHERE clause.<\/span><\/p>\n

Truncate will actually remove all of the rows from a table, and there will be no data in the table after we run the truncate command.<\/span><\/p>\n

TRUNCATE<\/strong><\/span><\/p>\n

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.<\/span><\/p>\n

TRUNCATE removes the data by deallocating the data pages used to store the table\u2019s data, and only the page deallocations are recorded in the transaction log.<\/span><\/p>\n

TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes, and permissions remain. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. As TRUNCATE TABLE is not logged, it cannot activate a trigger.<\/span><\/p>\n

TRUNCATE cannot be rolled back unless it is used in a TRANSACTION.<\/span><\/p>\n

TRUNCATE is a DDL Command.<\/span><\/p>\n

TRUNCATE resets the identity field of the table<\/span><\/p>\n

DELETE<\/strong><\/span><\/p>\n

DELETE removes one record at a time If used with a predicate in a where clause and records an entry in the transaction log for each deleted row.<\/span><\/p>\n

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.<\/span><\/p>\n

DELETE can be used with or without a WHERE clause<\/span><\/p>\n

DELETE activates triggers.<\/span><\/p>\n

DELETE can be rolled back.<\/span><\/p>\n

DELETE is a DML Command.<\/span><\/p>\n

DELETE does not reset the identity of the table.<\/span><\/p>\n

 <\/p>\n

 <\/p>\n

Thanks for reading this article,<\/strong><\/span><\/p>\n

Next steps :<\/strong><\/span><\/p>\n

    \n
  1. \u00a0<\/strong><\/span>Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n
  2. Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n

    \u00a0<\/strong><\/span><\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Solution The delete command removes the rows from a table on the basis of the condition that we provide a WHERE clause. Truncate will actually remove all of the rows from a table, and there will be no data in the table after we run the truncate command.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[173,65,4,32,3],"tags":[97,98,13,12,96],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/304"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=304"}],"version-history":[{"count":3,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/304\/revisions"}],"predecessor-version":[{"id":396,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/304\/revisions\/396"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=304"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}