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/

Is it possible to allow NULL values in foreign key? I s it possible to use different constraints for the same column in the table (i.e) (id int NOT NULL,UNIQUEUE)

1 Answers  

What are some of the pros and cons of not dropping the sql server builtinadministrators group? : sql server security

0 Answers  

How to assign new column names in a view?

0 Answers  

When we are using this query to shrink the log file,what exactly it will execute internally? Do we lose any data when we run this script? which data it will truncate in the log file and where it is saved. Please let me know... USE DatabaseName GO DBCC SHRINKFILE(<TransactionLogName>, 1) BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY DBCC SHRINKFILE(<TransactionLogName>, 1) GO

1 Answers   Cognizant,

Tell me can we use custom code in ssrs?

0 Answers  

What is an entity-relationship diagram (erd)?

0 Answers  

Explain transaction server isolation?

0 Answers  

let's assume you have data that resides on sql server 6.5. You have to move it sql server 7.0. How are you going to do it? : Sql server database administration

0 Answers  

How do you clear a log file?

0 Answers  

hi,i want t0 knom d syntax for a super key with an example

1 Answers  

Where can you find the error log information? : sql server database administration

0 Answers  

What is normalization and denormalization in sql server?

0 Answers