sql server commands

Question

What is difference between truncate and delete commands

Abhishek Singh · Answer

Hi Difference between Truncate and Delete in Sql server TRUNCATE 1. TRUNCATE is a DDL command. 2.R eset the indentity index. 3. Cannot use Where Condition. 4. It Removes all the data and 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 1. DELETE is a DML Command. 2. We can specify filters in where clause 3. It deletes specified data if where condition exists. 4. Delete activates a trigger because the operation are logged individually. 5. Slower than truncate because it keeps logs. 6. Rollback is possible. Hope this will help . Abhishek

Sahil Sharma · Answer

Truncate: It removes all rows from a table without a condition. It is a fast way of removing all the rows from a table. Truncate is a DDL command. Delete: It removes rows from a table using a where condition. It only removes the rows that matches the condition. Delete is a DML command.

Wim Sturkenboom · Answer

http://msdn.microsoft.com/en-us/library/ms177570.aspx For me the key points are: delete allows to delete an individual row and does not touch the identity counter; truncate deletes all rows and resets the identity counter.