sql server commands

Hi,

Difference between Truncate and Delete in Sql server

TRUNCATE
1. TRUNCATE is a DDL command.
2.Reset 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

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.

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.