wht is the difference bt delete and truncate ?

1. delete command is DML command bt truncate command is DDL command and can’t be rollback. It is auto commit.
2.DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

3.DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.

TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1. DELETE will not do this. In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: