what is difference between delete and truncet ?

Answer Posted / nitin bisht

Delete : A delete statement deletes the data from a table. A
delete statement can have a where clause, on which it
deletes the records that satisfy only the “where” condition.
If the
“where” condition is omitted the delete statement deletes
all the records in a table.

Actions of Delete statement :

* deletes the data from the current table space
* Modifies the undo tablespace with the delete records.
* Executes all the before / after statement and row
level triggers.
* Updates the indexes (makes the index empty if the
where clause is omitted).
* Constraint checks are performed before deleting the rows

.

Syntax of a delete Statement:

DELETE FROM table [WHERE condition]

Truncate : Truncate drops all the records in a table . But
as it is a DDL statement data cannot be retrieved. Usually
truncate is faster than delete statement becuase there is no
need to change or update the UNDO tablespace with the
deleted records. Truncate is an implict commit
Statement.Truncate statement deallocates the space.

Actions of Truncate Statement:Removes all the records from
the current tablespace.

* Updates the indexes.
* High watermark of the truncated table is reset.
* Integrity Constraint checks are performed

Syntax of a Truncate Statement:

TRUNCATE TABLE table_NAME ;

Is This Answer Correct ?    1 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What are the types of indexes that are present inside a database?

618


How to do performance tunning a database in DB2 V8.2 UDB in linux

1828


What is the difference between primary key, foreign key and candidate key?

612


Explain storage manager?

572


Explain the various types of normalization.

625






how do you do sql tuning?

1582


Which database does facebook use?

498


How do database connections work?

482


What do you mean by cluster and non cluster index?

555


query to Compare data of two tables between two different Databases(Oracle and SQL Server) Eg., Source Database: Oracle Table: Employee Target Database : SQL Server Table : emp

1620


please tell me the informatica telecom projects interview question? like if they ask about project explanation and the difficulties and the solutions taken to solve the issues for telecom projects,e.g i am workin for a vodafone project handlin postpaid section,pls tell me wat wud be the process if i work in this ? thanks

7995


Two tables emp(empid,name,deptid,sal) and dept(deptid,deptname) are there.write a query which displays empname,corresponding deptname also display those employee names who donot belong to any dept.

1358


Explain buffer manager?

536


What are the disadvantages of views in a database?

523


What is database bounce?

566