Answer / guest

Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.

Answer / sachin patil

Truncate is DDL Command while Delete is DML Command

Answer / kmbkrishnan

Delete Command require Log file updation for each row of
deleting process. But the Truncate command not.
So, the Truncate Command is so faster than Delete Command.

Answer / gk singh

truncate provide faster processing than delete because of
truncate does'n require transaction log uddation

Answer / vijay dev arya

Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will
actually remove all the rows from a table and there will be
no data in the table after we run the truncate command.

Answer / rahul goel

There are many differences. Some of these are as follows:
1. Delete can be rollback but Turncate can not be rollback.
2. Delete is a DML command and Turncate is a DDL command.
3. Delete command is used for deleting one or more row from
the table whereas Turncate command is used for deleting all
rows from the table.
4. Turncate is fact as compared to delete command.
5. we can use where clause with Delete but where is not
possible with Turncate.

Answer / kumar

Truncate will fail if the concerned table has foreign key
constraints while delete doesnt.

Answer / mohsin mohammed abdul

Delete command deletes the data from the tables .. but can
get back on issue of roll back whereas when you issue
truncate deletes the records and oracle implicitly issues
commit command and even when you issue rollback you won't
get the data back

Answer / afzal_aziz

Truncate:

- deallocates the data pages in a table and only this
deallocation is stored in transaction log

- aquires only table and page locks for the whole table.
since no row locks are used less memory is required (lock
is a pure memory object)

- resets identity column if there is one

- removes ALL pages. NO empty pages are left behind in a
table

- fast(er)

- doesn't fire delete triggers

Delete:

- removes one row at the time and every deleted row is
stored in the transaction log

- aquires table and/or page and row locks for the whole
table

- leaves identity column alone

- can leave empty pages in a table since empty page
removal requires a table lock which doesn't necessarily
happen

- slow(er)

- fires delete triggers


http://weblogs.sqlteam.com/mladenp/archive/2007/10/03/SQL-
Server-Why-is-TRUNCATE-TABLE-a-DDL-and-not.aspx

Answer / anil

Delete Commands:

it is dml statement
you can specify delete command with critery (where
condition)

syntax: Delete * from tablename where condition

Truncate Command:

it is DDL statement
you can not specify critery with truncate table

syntax: truncate table tablename

What does it mean to normalize a database and why would you do it?

0 Answers  

How to connect ms access to sql servers through odbc?

0 Answers  

What is difference between rownum and rowid?

0 Answers  

what is the main difference between after trigger and instead trigger.

2 Answers  

What is the difference between indexing and hashing?

0 Answers  

How to change the system date and time from SQL Plus Terminal ?

0 Answers   MCN Solutions,

if a parameter is not send to a stored procedure what type of identifier is to be used in Stp and if that parameter is not feed to the a query inside the Stp how to validate with out useing IF condition

1 Answers   Aviva,

You want to be sure that queries in a database always execute at the maximum possible speed. To achieve this goal you have created various indexes on tables which other statement will keep the database in good condition?

0 Answers  

What is live lock and deadlock? what is Lock escalation?

2 Answers   Microsoft,

What is a hint?

0 Answers  

Does an index slow down updates on indexed columns?

0 Answers  

What is dbcc? Give few examples.

0 Answers