| Author: sivangari 09 Jul 2008 | Member Level: Gold | Rating:  Points: 6 |
hi,
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.
|
| Author: Ratheesh 09 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
truncate is ddl command.its faster than delete as it doesnt
have go through the rollbacks etc.truncate being a ddl is
auto commit.we can only truncate the whole table(cant use
where clause).once table is truncated we cant rollback the
changes.when a table is truncated the memory occupied is
released.that id the water mark is adjusted.
delete is a dml command and can be rolled back.is slower
than truncate as it is dml has to go through rollback
segments etc.we can use where clause with delete.when a
table is deleted memory occupied is not released ans also
the water mark is not adjusted.
|
| Author: Deepa 09 Jul 2008 | Member Level: Diamond | Rating: Points: 4 |
TRUNCATE
1. It is DDL.
2. Speed is faster
3. Do not Check Constraints.
4. Roll Back is not possible.
5. Can not use where clause
DELETE
1. It is not a DDL
2. Speed is slow
3. Check constraints, If Exists then shoe error.
4. We can rollback .
5. User Where Clause
|
| Author: Deepa 09 Jul 2008 | Member Level: Diamond | Rating: Points: 1 |
TRUNCATE
1. It is DDL.
2. Speed is faster
3. Do not Check Constraints.
4. Roll Back is not possible.
5. Can not use where clause
DELETE
1. It is not a DDL
2. Speed is slow
3. Check constraints, If Exists then shoe error.
4. We can rollback .
5. User Where Clause
|
| Author: chandramohan 09 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
-----------
TRUNCATE is a DDL command and cannot be rolled back and All of the memory space is released back to the server. It can not use the Where conditions. DELETE is a DML command and can be rolled back. here can be use where conditions. TRUNCATE is much faster.
------------
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.
----------------
The Main Difference Between DELETE & TRUNCATE Are :-
[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command
[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records
[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions
[4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately
-------------
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
|
| Author: ramya 09 Jul 2008 | Member Level: Gold | Rating: Points: 3 |
delete:
1.deletes the data only
2. you can give the conditions in WHERE Clause
3.. We can rollback
4.slower
truncate:
1.deletes the data along with the structure also.
2. you cannot give conditions
3.. We cant rollback
4.faster
|
| Author: Pugalenthi 09 Jul 2008 | Member Level: Silver | Rating: Points: 3 |
Truncate:
1.Structure Of the table remains same
2.Faster
3.we cannot rollback
Delete:
1.Structure Of the table remains same
2.we can use where to delete a particular row
3.slow
4.we can roolback
|
| Author: Sridhar R 15 Jul 2008 | Member Level: Diamond | Rating: Points: 1 |
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Regards
Sridhar R
Nothing is illegal, Until You Get Caught
With Tears...Sridhar R
|
| Author: Ashok 15 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.
DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary. You can also specify a WHERE clause to narrow down the rows to be deleted. When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release using DBCC SHRINKDATABASE (db_name).
Please rate this post, if it is useful for you.
Thanks & Regards
Ashok
|
| Author: Vidhya 31 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
hi,
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
|
| Author: Vidhya 31 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
Whenever we delete records then they are stored in data
dictionary and can be retrieved later ie by roll back.
but when we truncate then the memory used by the records
which is to be truncated is released and it cannot be
rolled back.
when truncate command is issued, all the rows from the
table are deleted and the memory space occupied by the
table in the tablespace is also released whereas when
delete command is issued, only all the rows are deleted
fronm the table
|
| Author: Vidhya 31 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
hi,
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
|
| Author: UltimateRengan 08 Aug 2008 | Member Level: Diamond | Rating: Points: 4 |
Truncate is Data Dafanition Language.
Truncate command cannot be rollback
When Table is deleted memory occupied is released and also water mark is adjusted.
Truncate is faster than Delete.
Delete is Data Manipulation Language.
Delete is slower than Truncate.
Delete command is rollback.
When Table is deleted memory occupied is not released and also water mark is not adjusted
Advance Happy Diwali
SAP B1
|
| Author: Bunty 15 Dec 2008 | Member Level: Diamond | Rating: Points: 3 |
Hi,
The TRUNCATE and DELETE statement are identical in functinally. The differences are as follows,
1>TRUNCATE statement works faster than DELETE statement.
2>TRUNCATE statement will not fire trigger whereas DELETE statement will fire trigger.
3>TRUNCATE statement does not have a WHERE clause whereas IN DELETE statement WHERE clause is optional.
Thanks & Regards
S.S.Bajoria
|