Answer / nirmala.s

if u use delete to remove the data from the table,this will
delete the data.after using rollback the removed data can
be retrived.But in case of, truncate , it will delete the
data and that can't ever be retrived.even after using
rollback also. but the table structure of the table will
be there

if u are using drop command , this will delete entire
table.and its structure.

Answer / archana motagi

Both the operations can be rolled back.The difference is,
DELETE 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.

Answer / nageswarao

truncate is used for deleting all data from table.
it is similar to delete command without condition.
main differences b/w delete & truncate are
1.truncate is faster in execution than delete which will
restructure the complete table without bothering of data.
where as delete checks the data row by row wrotes
information into log and deletes.
2.with truncate we cannot use any conditions

Answer / monal

Delete doesn't reset identity column seed if you have 1
identity column in the table.
Truncate resets identity column seed to the original value.

Answer / firoz shaikh

If identity is set on column and current identity col is 10
then if truncate and delete is done then for truncate
identity col will be 1 and for delete then it will be 11.

Answer / mohammed

Guys, Please remember TRUNCATE CAN BE ROLLED BACK.

DELETE 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
De-allocation of the data pages of the table, which makes it
faster.

Because TRUNCATE TABLE is not logged, it cannot activate a
trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in
the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE
instead. If you want to remove table definition and its
data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Answer / shireen

TRUNCATE TABLE is functionally identical to DELETE
statement with no WHERE clause: both remove all rows in the
table. But TRUNCATE TABLE is faster and uses fewer system
and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records
an entry in the transaction log for each deleted row.
TRUNCATE TABLE removes the data by deallocating the data
pages used to store the table's data, and only the page
deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table
structure and its columns, constraints, indexes and so on
remain. The counter used by an identity for new rows is
reset to the seed for the column. If you want to retain the
identity counter, use DELETE instead. If you want to remove
table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a
FOREIGN KEY constraint; instead, use DELETE statement
without a WHERE clause. Because TRUNCATE TABLE is not
logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in
an indexed view.

Answer / hari

Delete - is logged operation,so deletion of each row get
logged into the transcation log.
Truncate - won't logged
Delete - use where condition
Truncate - Can't use where condition
delete - fire the trigger
Trunacte - can't fire the triggger

Answer / samba shiva reddy . m

DELETE TABLE is a logged operation, so the deletion of each
row gets logged in the transaction log, which makes it slow.
DELETE removes rows one at a time and records an entry in
the transaction log for each deleted row.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DDL command
example :
begin transaction deleteoncondition
select * from emp

Delete * from emp where empid = 1

rollback deleteoncondition
select * from emp
sp_help samba --Table structure


TRUNCATE TABLE deletes all the rows in a table, but it
won't log the deletion of each row, instead it logs the
De-allocation of the data pages of the table, which makes it
faster.

Because TRUNCATE TABLE is not logged, it cannot activate a
trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
TRUNCATE wont delete the structure of the table.

DROP will delete the structure of the table also.

Answer / manub22

–> DELETE:

1. Removes Some or All rows from a table.
2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.

–> TRUNCATE:

1. Removes All rows from a table.
2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
4. No Triggers are fired on this operation because it does not operate on individual rows.

Check more differences b/w DELETE & TRUNCATE here: http://sqlwithmanoj.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/

What are cursors in ms sql server?

0 Answers  

Which index created when Create Index on table(col), Why

2 Answers   CGI,

What is the process of normalising?

0 Answers  

Why do we use trigger?

0 Answers  

select top 5 * from emp order by newid() my question is , how this query get executed?

5 Answers  

hi, may i know what is the command to get abstract the current month, current year and current day from a given date.i want these three in a isolated way..seperatedly is that any way in sql server 2000

3 Answers  

Can a database be shrunk with users active?

0 Answers  

how can i store resumes in database?

3 Answers   HCL,

Working with TLogs

0 Answers  

What is replication with database mirroring? : sql server database administration

0 Answers  

What are tables in sql server?

0 Answers  

Explain primary key?

0 Answers