answersLogoWhite

0


Best Answer

TRUNCATE and DELETE:

It also removes the rows from the specified location in the query like DELETE command, but the only difference is :

1. rows once u truncated cnnot be ROLLBACKED like in DELETE. 2. which ROLLBACK can be possible with delete. 3. Truncate is used for HUGE databases or tables bcz it works efficiently. 4. DELETE will work efficiently on small data(tables).

Now it is vident that the only difference between TRUNCATE and DELETE is efficiency(i.e,Accessing Speed).

Annapurna.

TRUNCATE REMOVES ALL THE ROWS FROM THE TABLE AND CANNOT BE Rollbacked, while delete removes all/specific rows from table and can be rollbacked.

Also truncate resets the high water mark.

A common misconception is that they do the same thing. Not so. In fact, there are many differences between the two. DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted. You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place. 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.

The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

Truncate can be rollbacked,, basically i see lot of aritcles which says it can rollback,, but the truth is that we can rollback after trancate ,, difference is that in truncate only deallocation of the data page is done, but in delete the it is logged in deleted table and is physically removed from data page,,,

Harishjan

The difference is with operation on memory because if you want to have a place in memory for a later use you should use delete but if you want clear memory you should use truncate - this function clears the value and place where this value was stored.

For more detailed explanation about this topic please read the following article.

http://www.sqlservercentral.com/articles/delete/61387/

User Avatar

Wiki User

โˆ™ 2015-07-15 21:44:48
This answer is:
๐Ÿ™
0
๐Ÿคจ
0
๐Ÿ˜ฎ
0
User Avatar
Study guides

๐Ÿ““
Create a Study Guide

Add your answer:

Earn +20 pts
Q: How do you differentiate between truncate and delete?
Write your answer...
Submit
Related questions

Difference between truncate and delete?

Delete: When you use Delete command, it deletes all the data or some specific data from a table. It will not release the memory space used by those data. For that you can rollback the deleted data. Truncate: When you use Truncate command, it deletes all the data from a table and also releases the memory space occupied by those data. For that you can not rollback the data, when a table is truncated. ******************* Truncate: Cut off/cut short leaving a remnant. Delete: Remove completley.


Why use truncate over delete while deleting all rows?

For performance. Truncate will release the tablespace used by the table that the data is being removed from and delete will not release this space.


What is the difference between delete and truncate statement in SQL Database?

Delete statement deletes only the data from the table but you can apply some condition and only part of the data can be deleted. Truncate empties entire table. Drop deletes the table itself.


What is the difference between truncate and delete in terms of a database transaction?

Once data is truncated it can not be rolled back (recovered). However, data can be rolled back if deleted accidentally.


Will truncate and delete effect the structure of table in sql?

No. Truncate and delete are mainly data oriented operations. They do not affect the structure of the table in any way. It also does not affect the other database objects like primary key,index,constraint etc created on that particular table.


Which command will delete all data from a table and will not write to the rollback segment?

Truncate since it is DDL command


What is a sentence for the word truncate?

my brother can truncate my computer


The differentiate between ethics and law?

The differentiate between ethics and law?


Differentiate between Correcting spelling errors & Thesaurus?

Differentiate between Correcting spelling errors & Thesaurus?


Differentiate between general and specific reserve?

differentiate between general and specific reserve?


Differentiate between javascript and HTML?

Differentiate a pilot and a plane!


A sentence for differentiate?

He could not differentiate between what was right or wrong.


How do you empty a file using C programming?

That depends on the type of file and what you mean by "empty". Generally, fopen() called on an existing file, with the "w" option instead of the "a" option will truncate the file to zero length. To delete a file completely is a different process. Function truncate is your friend.


What does the word 'truncate' mean?

Truncate has many meanings depending on how it is used in a sentence. If it is used as a verb, truncate means to shorten something. If used as an adjective it means it stopped suddenly.


Can you use commit statement after truncate?

You can use COMMIT any time, but it is not necessary after TRUNCATE TABLE.


Differentiate between transparent bridge and spanning bridge?

Differentiate between a simple bridge and transparent bridge


Differentiate between marketing policy and marketing strategy?

Differentiate between marketing policy and marketing strategy


Differentiate between physical assets from physical liabilities?

differentiate between physical assets from physical liabilities


Differentiate between quantitative and real time PCR?

: Differentiate between quantitative and real time PCR.


What is the difference between drop and truncate in dbms?

even though both these commands are related to the structure of the table...,we can regain the data when we use use drop command by use of rollback command but its not possible in the case of truncate command.


How would you use truncate in a sentence?

She had to truncate her story, so that it would fit into the five minute session.


Differentiate between third generation of computers and fourth generation?

Differentiate between third generation of computers and fourth generation?"


What does truncate mean in math terms?

truncate means to add the whole number to whatever else you have,and then multiply it by your fraction


Difference between format and delete?

What is the difference between delete and format?


Can you give some example sentences using the word 'differentiate'?

Can you differentiate between the different types of hosta?Can you differentiate between the different types of birds in this yard?