Query Hints (Optimizer)

Query Hints (Optimizer) is a SQL statement in the SELECT Queries category. SQL Server: optimizer hints that force specific index usage, join strategies, or locking behavior. The syntax is SELECT * FROM table WITH (INDEX(index_name), FORCESEEK);. It returns optimized query result. A typical example: SELECT * FROM employees WITH (INDEX(idx_last_name)) WHERE last_name = 'Smith'; -- Force use of idx_last_name index SELECT * FROM orders WITH (NOLOCK) WHERE status = 'pending'; -- Dirty read hint (READ UNCOMMITTED) SELECT * FROM table1 INNER LOOP JOIN table2 ON table1.id = table2.id; -- Force loop join strategy -- Use hints sparingly — only when optimizer makes poor choices A close relative is SELECT, which retrieves data from a table. The most fundamental SQL statement. A close relative is SELECT *, which selects all columns from a table. A close relative is SELECT DISTINCT, which returns only distinct (unique) values from the specified columns.