DF211: Index hint is used.

Last modified: December 25, 2024

The topic describes the DF211 T-SQL code analysis rule.

Category

PERFORMANCE

Message

Index hint is used.

Description

For better performance, avoid using index hints.

Additional information

It is worth mentioning that using index hints can have implications for performance and query optimization. While index hints can sometimes be useful in guiding the query optimizer to choose a specific index, they can also lead to suboptimal query plans and hinder the database’s ability to adapt to changing conditions or statistics. Instead, it is generally recommended to rely on the query optimizer’s capabilities to choose the most efficient execution plan based on available indexes, statistics, and query parameters.

Noncompliant code example

UPDATE d
SET txt = 'sample'
FROM dbo.DemoTable d WITH(INDEX(MyIndex))
WHERE id = 1
GO

Compliant solution

UPDATE d
SET txt = 'sample'
FROM dbo.DemoTable d
WHERE id = 1
GO