Delete:
delete rows and space allocated by mysql
data can be roll back again
you can use it with WHERE clause
Truncate:
It is similar to delete. But the difference is you can't roll back data again and you cann't use WHERE clause with it.
---------------------------------------------------------------------------------------------
delete rows and space allocated by mysql
data can be roll back again
you can use it with WHERE clause
Truncate:
It is similar to delete. But the difference is you can't roll back data again and you cann't use WHERE clause with it.
---------------------------------------------------------------------------------------------
Truncate:
- Works by deallocating all the data pages in the table.
- Will delete all data - you cannot restrict it with a WHERE clause.
- Deletions are not logged.
- Triggers are not fired.
- Cannot be used if any foreign keys reference the table.
- Resets auto id counters.
- Faster.
Delete:
- Works by deleting row by row.
- Can be restricted with a WHERE clause.
- Deletions are logged in the transaction log (if you have logging on obviously) so the delete can be recovered if necessary (depending on your logging settings).
- Triggers are fired.
- Can be used on tables with foreign keys (dependant on your key cascade settings).
- Slower.
0 comments:
Post a Comment