Post reply

Delete Vs Index

  • December 1, 2014 at 3:00 am

    #299000

    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

    #1761631

    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

    #1761635

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply