« March 2007 | Main | May 2007 »
April 6, 2007
Are DELETES expensive?
There were internal discussions on deleting rows from database tables. Here are some points:Deletes are slower because they are logged in the transaction log. However if you use Truncate they are much faster as it is not logged in the transaction log but you need the necessary privilege to do Truncate. So if there are a lot of deletes then delete is relative slower/expensive than truncate..
Delete will enforce an exclusive lock ... the granularity of this lock depends on the number of rows affected by the delete. SQL Server, when possible will only exclusively lock rows. There is an internal threshold in which SQL Server promotes all of the row level locks to a single page level lock. Further, there is an internal threshold that when reached makes SQL Server promote all of the page level locks to a table level lock.
Can deletes be expensive ? Yes of course they can, but it's hard to generalize without knowing how many rows are affected by the operation.
Share:
Posted by Steve Mann at 4:29 PM | Comments (0)
