December 1, 2014 at 3:00 am
Hi There,
Can anyone share the links to read about HOW index affects delete operation in sql server ?
* I have a table, sometimes(rarely)I do delete on that table. But only little amount of data.
* Table have a clustered index.
* Delete will be based on the non key column
* Am I needed to create an index on the column on which delete is based.
* Or table scan is alone helpful for delete(currently using the table scan to delete a particular record)
Thanks in advance
December 1, 2014 at 3:05 am
If there is only a little amount of data, I wouldn't lose too much sleep over it.
A table scan will be quick enough.
Remember that if you add an index, it might help locating the row to delete, but the index itself has to be maintained as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 1, 2014 at 3:35 am
The trick would be to look at the execution plan to understand how your delete statement is being processed. Table scans can be costly depending on the size of your table and you may want to offset that cost with an index. But, in order to understand if that's going to help or hurt, you need to know what the performance metrics are to start with. Get the execution plan, the number of reads and writes and the amount of time the query takes. Then add the index and compare the two. Preferably, do this on a test system. Then figure out if you're going to be using that index elsewhere, or if it's only for deletes. If your deletes are rare, it may not be worth the cost as was already pointed out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply