Difference between Delete and Truncate?

ganeshMohan

hai if anyone known the difference between the delete and truncate and please give me an example thanks in advance Ganesh Mohan

John ph

Delete and Truncate performs Identical tasks. but there are differences... Delete is a DML command used to delete a particular record or some set of Records based on the WHERE-Clause. And It can also Delete all the Records when there is no WHERE-Clause specified. The Delete operation performed using Delete Command can be Rolled Back. Truncate is a DDL Command used to delete all the Reocrds from the Table and also it Reinitializes the Table. Primary Key Identity values will be regenerated from the initial value for the subsequent entries after deleting all reocrds. WHERE-Clause can't be used in Truncate Command. The Delete operation performed using Truncate Command can't be Rolled Back. Truncate is much faster than Delete Note : Truncate = Delete + Commit

Regards
J O H N :rose:
"Even eagles need a push." David McNally

Pete OHanlon

Good answer. Get's a 5 for its clarity and completeness.

Deja View - the feeling that you've seen this post before.

John ph

Thanks Pete.

Regards
J O H N :rose:
"Even eagles need a push." David McNally

Pete OHanlon

You're welcome. It's always a pleasure to read a detailed and accurate answer.

Deja View - the feeling that you've seen this post before.

Mike Dimmick

One other difference is that the user doing the TRUNCATE must have greater permissions than doing a DELETE. DELETE permissions can be granted to a user or role on a per-table basis; on SQL Server 2000, TRUNCATE permissions are only granted to the owner of the table, members of the sysadmin server role, and members of the db_owner or db_ddladmin fixed server roles. SQL Server 2005's documentation is a little unclear, it says that the user must have ALTER permissions on the table but then repeats the sentence from SQL Server 2000 Books Online. Obviously if you're connecting to SQL Server using sa or a trusted connection (Windows authentication) using an administrator account, you have those permissions. However, this is not a good practice as those accounts have full control over the entire database server, and as such, a mistake in your application logic could lead to accidental, or even deliberate, damage. In my view, you should firewall what an application can do by setting up less-privileged server logins and database users.

DoEvents: Generating unexpected recursion since 1991

kubben

I think one other difference is that Delete is logged and truncate is not. Thus the ability to roll back etc. That is also part of the reason that truncate is faster. Ben

kubben

Ok so I guess part of it is logged. I had to look it up. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. Ben

ganeshMohan

Thanks John

Sathesh Sakthivel

Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it. Truncate - delete all records from table There is no rollback it always commit without givening the commit.

SSK. Anyone who says sunshine brings happiness has never danced in the rain.