SQL 2008 Truncate vs Delete
-
What is the difference between truncating and deleting a table? After the truncating or deleting do we need to re-index or shrink the table? We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"? Thanks!
-
What is the difference between truncating and deleting a table? After the truncating or deleting do we need to re-index or shrink the table? We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"? Thanks!
mrfalk wrote:
re-index
If there's no records left, there's little to reindex.
mrfalk wrote:
What is the difference between truncating and deleting a table?
Google "MSDN Truncate", and from the manual we learn;
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
mrfalk wrote:
re-index
If there's no records left, there's little to reindex.
mrfalk wrote:
What is the difference between truncating and deleting a table?
Google "MSDN Truncate", and from the manual we learn;
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
So if we are using the Delete statement with the WHERE clause is it necessary to reindex? Is there a "standard" for when to reindex files? After further research we will be using the delete clause because we want to retain a given number of days data. Within that given number of days data we will also be deleting specific rows based on a given parameter. We just want to make sure we are covering all bases.
-
So if we are using the Delete statement with the WHERE clause is it necessary to reindex? Is there a "standard" for when to reindex files? After further research we will be using the delete clause because we want to retain a given number of days data. Within that given number of days data we will also be deleting specific rows based on a given parameter. We just want to make sure we are covering all bases.
mrfalk wrote:
So if we are using the Delete statement with the WHERE clause is it necessary to reindex?
Yes.
mrfalk wrote:
Is there a "standard" for when to reindex files?
No. Start here[^].
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
What is the difference between truncating and deleting a table? After the truncating or deleting do we need to re-index or shrink the table? We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"? Thanks!
-
What is the difference between truncating and deleting a table? After the truncating or deleting do we need to re-index or shrink the table? We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"? Thanks!
We have found that for integrated security through VB.NET the DELETE works with less permissions. The error you get back when using TRUNCATE gives no indication as to truncate being the problem.
-
mrfalk wrote:
re-index
If there's no records left, there's little to reindex.
mrfalk wrote:
What is the difference between truncating and deleting a table?
Google "MSDN Truncate", and from the manual we learn;
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Truncate statement: 1. Deletion is very faster. Almost no (very minimal) transaction log is produced during this operation. 2. Data pages which are used by the table are de allocated for further use by SQL Server in other operations. 3. Identity column value is reset from its original default position. For example if a table has an identity column and identity value start form 1 then after running truncate command the value start incrementing back from 1. 4. As wiping out rows are very faster, so the number of locks are low. Although during TRUNCATE operation table and page lock happens but not each row. 5. TRUNCATE TABLE command does not support where clause, it also does not works if foreign key exists in the table. In addition, table participates in log shipping or replication also does not honor TRUNCATE table command. 6. Records removed by the TRUNCATE COMMAND cannot be restored even though the database recovery model is set to FULL. 7. TRUNCATE statement also does not fire triggers. DELETE Statement: 1. The DELETE statement removes rows one at a time. For each deleted row the operation writes an entry in the transaction log. 2. DELETE operation is more resource intensive thus consumes more database resources and locks. 3. Where clause can be included with the statement to restrict the number of affected rows. 4. Internally the DELETE operation does not cleanup rows immediately if the table has any index on it, The operation marks the affected rows "to be deleted". The marked records are known as GHOST RECORDS. Although these records are de allocated quickly by a background cleanup process for better performance.
dsdf
-
What is the difference between truncating and deleting a table? After the truncating or deleting do we need to re-index or shrink the table? We are looking to do some clean-up on some rather large tables on our database. Once we do the clean-up what steps do we need to preform for overall database "health"? Thanks!
Hi, -- DELETE, TRUNCATE and DROP Statements DELETE /* The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. */ TRUNCATE /* TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. */ DROP /* The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. */ --Difference between TRUNCATE and DELETE commands /* 1) TRUNCATE is a DDL command whereas DELETE is a DML command. 2) TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace. Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data. 3) You cann't rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently. 4) In case of TRUNCATE ,Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired. 5) You cann't use conditions(WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause */ Regards, GVPrabu