Tuesday, August 18, 2009

SQL Truncate Vs Delete

1. Truncate will be most faster since it won't log more information while deleting the data.
2. We can't use WHERE clause in Truncate
3. Truncate statement will not fire the trigger.
4. While using truncate statement the table identity column counter reset to 0. But in the case of delete it will maintain the old counter value.
5. You can't use truncate if the table referenced by a foreign key constraint. 6. Both we can Rollback.

CREATE TABLE TruncateTest ( id int )
INSERT INTO TruncateTest VALUES(1)
SELECT * FROM TruncateTest

BEGIN TRANSACTION;
TRUNCATE TABLE TruncateTest
ROLLBACK TRANSACTION
SELECT * FROM TruncateTest

DROP TABLE TruncateTest

No comments:

Post a Comment