DF212: Inefficient use of a table hint.

Last modified: December 25, 2024

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

Category

PERFORMANCE

Message

Inefficient use of a table hint.

Description

Avoid using table hints such as NOEXPAND, INDEX, FORCESEEK, FORCESCAN, IGNORE_CONSTRAINTS, and IGNORE_TRIGGERS, as they can limit the query optimizer and result in inefficient performance.

Additional information

This rule is applied to the following hints:

• NOEXPAND
• INDEX
• FORCESEEK
• FORCESCAN
• IGNORE_CONSTRAINTS
• IGNORE_TRIGGERS

Table hints force specific behaviors, preventing the optimizer from considering alternative and potentially more efficient query plans. The optimizer may be better suited to decide on the best indexes, scans, or seeks based on the data and statistics available. By manually enforcing certain hints, you might inadvertently choose a strategy that works for a specific case but fails to perform well as data changes over time. For instance, hardcoding hints like INDEX ties the query performance to the current database state, making it less adaptable to changes in schema, index structures, or data volume.

Forcing specific operations like FORCESEEK or FORCESCAN may result in the query using an index or scan that isn’t the best option for the data size or distribution at that moment.

Hints like IGNORE_CONSTRAINTS and IGNORE_TRIGGERS bypass important integrity checks, which can lead to inconsistent or incorrect data if these constraints or triggers were expected to run.

Noncompliant code example

SELECT
  id
FROM dbo.DemoTable WITH (FORCESEEK);
GO

Compliant solution

SELECT
  id
FROM dbo.DemoTable;
GO