DF212: Inefficient use of a table hint.

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

Want to Find out More?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by dbForge Studio for SQL Server.
All Features

All features

Get acquainted with the rich features and capabilities of the Studio in less than 5 minutes.
Request a demo

Request a demo

If you consider employing the Studio for your business, request a demo to see it in action.
Ready to start using dbForge Studio for SQL Server?
Download a free 30-day trial