Search code examples
sqlsql-serversql-delete

Delete 'WITH INDEX' SQL query

Normal DELETE : 

DELETE 
FROM Table
WHERE   column1 =   'some value'

DELETE WITH INDEX : 

DELETE  Table1
FROM    Table1  WITH(INDEX(PK_Table1))
WHERE   column1 =   'some value'

I know that when you delete a row from a table it will search for all reference tables, which slows down delete execution.

How does delete using WITH INDEX work?

Solution

  • WITH(INDEX(PK_Table1)) is an index hint; i.e. tells SQL to use a specific index rather than trying to determine the best one. Generally index hints aren't needed; SQL is very good at knowing what's best; and doesn't take much time working out the best index, since it recalls which index it used the last time it had a similar query.

    There are some cases where SQL may get it wrong; for those you could improve performance by adding a hint. These cases are very rare. In such cases, test thoroughly, and with data as close to real-world as possible (as what data's in the table will impact how well the query performs under each index

    Unless you're seeing a real performance problem & can prove that your hint helps, don't use the hint.

    Here's a related post on hints, which summarises the argument well: https://www.brentozar.com/archive/2013/10/index-hints-helpful-or-harmful/