Answered On : Mar 11th, 2006
DELETE: when u r using delete command in oracle the data will be deleted in the table. after that if u r using rollback the data in the table will be retrived.TRUNCATE: in the case of truncate, when u r using truncate what ever data in the table it will be deleted. after that if u r giving rollback also the data will not be retrived. but the structure of the table is available.DROP: in the case of DROP, if u r using drop the rollback command is not working and structure and data in the table also deleted. nothing willbe available about that table.EXAMPLE:DELETE: delete * from emp; "then the data inthe table will be deleted"after this u give ROLLBACKthen the data willbe retrieved. TRUNCATE: truncate table emp; " then the data inthe table willbe deleted" but the sturucture is available> if u type--- desc empthen it give the structure of that table.DROP: drop table emp:after that what ever command u r using on emp table it will give error. bcuz the table object emp is not available in database
Answered On : Mar 16th, 2006
The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database. Delete is not effective when you use it to remove all data from a table, because it takes up storeage spaces due to unclear memories. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it free table storage spaces and only use it when you need to remove all data from a table. The drop statement will result in completely removing the table from the database. Note: Statements above are made with the assumption that a commit is has been executed. Once a commit statement is executed (commit is final). Therefore, rollback a transaction will not work if commit statement is executed.
Delete command: It actually removes the data permanently from the table with user specified conditions until you commit your work.It you want to save ,you could rollback the command. It will also create the space in the tablespace which in turn in a datafile.Drop command : It will remove both the structure and data in the specified table and could not be rolled back as it is a DDL command.Truncate : It will remove full data from the table without any user specified condition. It cannot be rolled back.
Answered On : Mar 27th, 2006
Hi drop deleting the table and its structure from the data basedelete command used for deleting the records from the table,and it removing the table space which is allocated by the data basetruncate is also delete the records but it is not delete the table space which is created by the data base.note:Here For every table in the DB the DB allocated some default space for that table.ThanksRam
Answered On : Apr 17th, 2006
THE Ariticle is wrong even in Truncate the transaction can be rolled back
Answered On : Jun 27th, 2006
HiI dont think there is any possibility of rolling back a truncated transactionthrough rollback command ...if requires one can recover it through other wayseither via a point in time recovery using incomlete recovery...but as per oracle syntax rollback is concerned no body can recover a truncated tables data through rollback command.See Delete Deletes the Tables data either selective or all and put the data in an rollback segment as well where from a read consistent view or a flashback view can be seen even after commit and one can rollback the tables data after delete as well. one more thing delete doesn' resets the HWM and space occupied with insert can't be reclaim by delete.when you deletes table data indexes are not dropped.Its a DML statementDrop drops all tables with its data structure idexes and integrity constraints.Truncate command removes all the data from table and drops all integrity constraints its an DDL Statement and resets the HWM but you can't rollback a tables data after truncate and one more thing truncate doesn' remove selective rows it can only removes the full tables data.truncate leaves the database structure ad is a good tool to reset HWM.but a dangerous tool for database security.
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. The operation cannot be rolled back. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.From Oracle 10g a table can be "undropped". Example:SQL> FLASHBACK TABLE emp TO BEFORE DROP;Flashback complete.
Answered On : Aug 9th, 2006
This article will be very useful for me. It 's a good explanation for sql. thank u
Answered On : Aug 23rd, 2006
Delete : A DML Statement creates a undo log . deletes record from table . Can be rolled back.Truncate : A DDL it actually sets the high water mark level of the table back to zero (depends on MINEXTENTS too) , hence no one can read the table records.The commend get itself issues a commit as it is DDL command hence can not be rolled back . Unlike delete truncate can not be selective . Table defination remains as such. It is fast as no logs are maintained.DROP:drops a table completelty (with table structure)can not be rolled back . can be cascaded.
I dont know about Oracle. But acccording to sql serverDELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
Answered On : Apr 30th, 2007
delete: removes a sigle row from the table; truncate: removes all rows from the table,the table view exist drop: completely remove table from the database,the table viw not exist
Delete:1)It uses where clause. 2)It makes an entry in the transaction log each time it deletes a row,hence is time taking.drop: It deletes the structure as well as the data.truncate: 1)It uses no where clause. 2)It makes use of less system and tansaction log,hence is faster compared to delete. 3)you can't delete the structure.
Answered On : Nov 21st, 2007
Yes you can rollback the truncate command in SQL 2000
It is possible to rollback the truncate operationIf you defined your truncate command within a begin and end transaction then it is possible to rollback the truncate operationCheck the below code :create table sampletest(id int identity(1,1),Name varchar(50))insert into sampletest(Name)values('Paresh')insert into sampletest(Name)values('Naresh')insert into sampletest(Name)values('Suresh')insert into sampletest(Name)values('Ramesh')insert into sampletest(Name)values('Kalesh')select * from sampletestbegin trantruncate table sampletestrollback
Delete: DML statement, can rollback and specify where clause.Truncate: DDL statement, can't rollback, cannot specify where clause, retains table structure, deletes all rows.Drop: DDL statement, can't rollback, cannot specify where clause, deletes table structure, deletes all rows.
DELETEDelete keyword is used to delete some row from table with
conditionally or all rows from table.
Like example:SELECT COUNT(*) FROM
DELETE FROM employees WHERE job_id='it_back';
some rows are deleted from employees where job_id is it_back.
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees;
rollback is applicable on delete keyword.but if we use commit after 2no statement, then we will get after execute select count(*) from employees - 14
DROPDrop is a keyword which is used to drop a table from database. it
makes free the storage of table.
DROP TABLE employees;
SELECT * FROM employees; it will be show a error.
SELECT * FROM employees;it will
show error because drop statement is completed auto commit;
Truncate is used to delete all rows from table. It does not use condition to delete rows from table.
It is also completed auto commit statement;
TRUNCATE TABLE employees;SELECT *
FROM employees; No row is selected
ROLLBACK;SELECT * FROM employees;
No row is selected
DELETEWe can use the DELETE command to remove rows from a table. We must specify a WHERE clause in our DELETE statements so only some rows will be removed.If we do not do that all rows will be removed and that something we most certainly never want. DELETE operations will cause all DELETE triggers on the table to fire.TRUNCATEWe can use the TRUNCATE statement to remove all rows from a table. A very important thing to note is that the TRUNCATE operation cannot be rolled back and no triggers are fired. DROPThe DROP statement removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. The operation cannot be rolled back and no triggers will be fired.
Truncate can be rolled back. DDL command.Delete cannot roll back. DML command.Drop cannot roll back. DDL command.
DELETE is used to delete all records in the table. It is a DML command while DROP deletes the structure of the table. TRUNCATE is much faster than DROP and it cannot ROLLBACK.
Answered On : Jul 17th, 2011
(1)Drop and Truncate both are DDL (Data Definition Language) commands while Delete is a DML(Data Manipulation language) command.
(2)Drop and Truncate commands can't be rolled back once implemented while Delete command can be rolled back.
(3)Drop command deletes all the things belonging to the table (schema, metadata,complete table structure and data) while Truncate removes all the rows from the table .
(4)Delete command removes the rows specified in the where condition. If NO WHERE condition is specified then all the rows are removed from the table . But, the operation can be rolled back if we want to undo the changes. If we really want to keep the changes we have done then commit is required.
Delete and Truncate Both Can be Rolled back
- CREATE TABLE sampletest(id int identity(1,1),Name varchar(50))
- INSERT INTO sampletest(Name)VALUES(Paresh)
- INSERT INTO sampletest(Name)VALUES(Naresh)
- INSERT INTO sampletest(Name)VALUES(Suresh)
- INSERT INTO sampletest(Name)VALUES(Ramesh)
- INSERT INTO sampletest(Name)VALUES(Kalesh)
- SELECT * FROM sampletest1
- SELECT * INTO sampletest1 FROM sampletest
- begin tran
- DELETE sampletest1
--Delete is a DML Operation while Drop/Truncate are DDL operation
--Delete can be rolled back but Drop/Truncate can not be rolled back
--On Delete, transaction logs are written but Drop/Truncate does not write transaction logs
--Delete operation is applied on the data inside a table with where condition (may or may not), Truncate operation is used to remove ALL the data from a table (no where condition is allowed) and Drop is used to drop a Database Object like Table,view, Store Procedure etc.
Answered On : Feb 8th, 2012
Truncate table command can be rolled back.
Create a table named as test
insert some records into this table
After insertion run select * From test to check insertion of records in the table
then execute these lines
truncate table test
execute - select * From test, to check deletion of records
the execute this:
Answered On : Mar 2nd, 2012
First of all delete is DML command and truncate is a DDL command requirement is DML command does rollback but DDL command does not rollback ....
Answered On : May 22nd, 2012
Drop : Deletes both Table Structure and the Data
Truncate : Deletes only Data and it performs automatic Commit
Delete : Deletes only Data and Doesn't performs automatic Commit.
Truncate is much faster than delete. all same ans like above
Answered On : Jun 5th, 2012
In truncate all the data will be deleted whereas in delete also same but the difference in delete we again reback the data by using flashback (without use commit) in truncate it is not possible while drop the total table is drop
Answered On : Jun 18th, 2012
Delete: delete a single row or multiple row
it is DML operation so rollback is enable.
Truncate: delete from all record in table. commit or rollback is not using. trigger will not fired.but column will there
Drop : delete a table structure. commit or rollback is not working. trigger also not working.
Answered On : Nov 21st, 2013
Delete is DML statement which delete the record/s and can be Rolled Back.
Truncate does same work but because its a DDL statement so cannot be Rolled Back.
Drop is DDL statement which delete the Table structure witht he data, Cannot be Rolled Back.