Difference b/w Truncate and Delete

Question

I READ MANY ARTICLES FROM WEB WHICH POINTING OUT MANY DIFFERENCE ...

SOME OF THEM ARE FINE .. BUT I GOT CONFUSED DIFF W.R.T. ROLLBACK

I THINK THEY EXPLAIN A WRONG DIFFERENCE " DELETE IS ROLLBACK BUT TRUNCAT IS NOT "

BUT WHEN I DID ACTUAL IMPLEMENTATION IT'S GIVE ME UNEXPECTED OUTPUT ............ BOTH TRUNCAT AND DELETE GETS ROLLBACK..

PLEASE ANY ONE HAVE THE SOME EXPLANATION ABOUT THIS QUERY .........

REPLY ME SOON .. THANK YOU

Manoj Bhoir · Answer

Hi DELETE 1. DELETE is a DML Command. 2. DELETE statement is executed using a row lock each row in the table is locked for deletion. 3. We can specify filters in where clause 4. It deletes specified data if where condition exists. 5. Delete activates a trigger because the operation are logged individually. 6. Slower than truncate because it keeps logs. 7. Rollback is possible. TRUNCATE 1. TRUNCATE is a DDL command. 2. TRUNCATE TABLE always locks the table and page but not each row. 3. Cannot use Where Condition. 4. It Removes all the data. 5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. 6. Faster in performance wise because it doesn't keep any logs. 7. Rollback is not possible. DELETE and TRUNCATE both can be rolled back when used with TRANSACTION. If Transaction is done means COMMITED then we can not rollback TRUNCATE command but we can still rollback DELETE command from LOG files as DELETE write records them in Log file in case it is needed to rollback in future from LOG files. Reference : http://www.codeproject.com/Questions/370759/Difference-between-delete-and-truncate

Rajeesh Menoth · Answer

Hi Ref this : http://dba.stackexchange.com/questions/995/how-do-i-get-back-some-deleted-records

Rajeesh Menoth · Answer

Hi 1)Delete keep the lock over each row where Truncate keeps the lock on table not on all the row. 2) Counter of the Identity column is reset in Truncate where it is not reset in Delete. 3) Trigger is not fired in Truncate where as trigger is fired in Delete. 4) In TRUNCATE we cannot rollback. 5) In DELETE we can rollback Check the below links also : 1) http://www.essentialsql.com/what-is-the-difference-between-truncate-and-delete-in-sql-server/ 2) http://www.softwaretestingclass.com/difference-between-truncate-and-delete-statement-in-sql-server/ 3) http://www.c-sharpcorner.com/UploadFile/65fc13/difference-between-delete-truncate-and-drop-statements-of-m/

Manoj Bhoir · Answer

Hi If you delete data using DELETE query before commit you can use ROLLBACK. Check this link : Recover deleted data in SQL Server

Durga Velusamy · Answer

Delete & Truncate both are doing same function. Delete & Truncate both supported in sql server(database). Truncate is not supported in odbc(database) Delete: its delete all the data in a table . query-:Delete from tablename; Truncate: its delete all the data in a table. query-:Truncate from tablename; Rollback: which is used in oracle database. its delete all activities which is you have done before. Oracle have RollbackSavepointCommit. Commit-: should give commit after given insert query then only it will store in database. Savepoint-: Savepoint savepointname- if give insert query after savepoint savepointname. Savepoint savepointname. insert into tablename values(); now giving Rollback means like Rollback savepointname-:its delete only the actions which you have done after that the savepointname. Commit savepointname-its store only the actions which you have done after that the savepointname.

DeepAk BhAtt · Answer

THANK YOU MANOJ .... BUT I HAVE SOME MORE CONFUSION ... HOW CAN I RECOVER DATA FROM LOG WHEN DATA GETS DELETED WITH DELETE QUERY

DeepAk BhAtt · Answer

THANK YOU MANOJ .... BUT I HAVE SOME MORE CONFUSION ... HOW CAN I RECOVER DATA FROM LOG WHEN DATA GETS DELETED WITH DELETE QUERY

Rakesh · Answer

Check it this link: http://www.essentialsql.com/what-is-the-difference-between-truncate-and-delete-in-sql-server/