SQL Server Delete 'WITH INDEX' SQL query

fdbelqdn 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(168)

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?

sql-server

来源：https://stackoverflow.com/questions/47071889/delete-with-index-sql-query

2条答案

按热度按时间

yhqotfr81#

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/

赞(0）回复(0）举报 2023-11-16

bxfogqkk2#

By defining an alias you can use an index table hint on delete statement:

DELETE X
FROM   Table1 X WITH(INDEX(IX_Table1_Column1))
WHERE  Column1 = 'some value'

Without using alias you get an error like that:

Msg 1069, Level 15, State 1, Line 1
Index hints are only allowed in a FROM or OPTION clause.

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Delete 'WITH INDEX' SQL query

2条答案

相关问题

热门标签

最新问答