Compare and contrast TRUNCATE and DELETE for a table.
Answers were Sorted based on User's Feedback
Basically truncate is a DDL and Delete is DML. You can use
delete to remove rows depending on certain criteria and it
generated redo logs and can be rolled back before you
commit the transaction, Where as the truncate is used to
remove all the data in the table. Once you execute this
command u can not rollback this transaction.and commit is
not required for the truncate statement
Is This Answer Correct ? | 10 Yes | 0 No |
Answer / taral desai
truncate is faster then delete... truncate don't generate
rollback. truncated data can't be recovered
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / amsu
Truncate is a DDL and delete is a DML command. Once you
truncate a table you cannot rollback where as you can
rollback from the delete command. When you truncate a
table it release the space and it resets the highwater
mark. Delete won't release the space and it won't resets
the highwater mark.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / sridhar
truncate and delete are deltes all rows in the table but truncate is much faster than delete.
and if u delete row in the delte deletion of each gets logged in the transaction log.
truncate there is no way to log in transaction log
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / kishore
truncate removes all the rows from a table
it can't be rollback
delete removes all /specific rows from a table
it can be rollback
drop removes whole table
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / monal
IN MSSQLSERVER TRUNCATE RESETS IDENTITY VALUE OF COLUMN
WHILE DELETE DOESN'T RESETS IDENTITY VALUE OF COLUMN
Is This Answer Correct ? | 0 Yes | 1 No |
How does Oracle guarantee data integrity of data changes?
How would you best determine why your MVIEW couldn't FAST REFRESH?
What are the high-availability solutions in sql server and differentiate them briefly? : sql server DBA
When creating a user, what permissions must you grant to allow them to connect to the database?
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
mssqlserver 2005 Backup Advantages & Disadvantages? mssqlserver 2005 Restore Advantages & Disadvantages? mssqlserver 2005 Recovery mode Advantages & Disadvantages? mssqlserver 2005 Mirroring Advantages & Disadvantages? mssqlserver 2005 Replication Advantages & Disadvantages? mssqlserver 2005 Logshpping Advantages & Disadvantages? mssqlserver 2005 Job Advantages & Disadvantages?
why do we give pctfree and pctused in create table statement?
what do you understand by fine-grained auditing?
how can you record information about current session?
What is the difference between Datapump & export/import?
What are the different types of upgrades that can be performed in sql server? : sql server DBA
Where do you find the default index fill factor and how to change it? : sql server DBA