Not everyone knows that query level hints (like loop join) will impact the entirety of a DML query plan. This includes foreign key validation and indexed view maintenance.
Let us look at an example with two tables involved in a foreign key constraint.
create table department(deptid int primary key clustered, deptname varchar(10))
create table employee(empid int primary key clustered, empname varchar(10), deptid int references department(deptid))
insert department values(1, 'Optimizer')
At first glance, it might seem useless to provide a join hint for a scalar insert - like when inserting a row to the employee table – because the query contains no joins. However, this can make sense in presence of foreign key validations, because the Optimizer will automatically augment the query plan with a join for the purpose of validating the constraint. For example, this insert statement here
insert employee select 1 empid, 'Stefano' empname, 1 deptid option (merge join)
will produce a plan with a merge join between the employee and department tables. The join will enforce that the value of the deptid column actually exists in the primary table, department. The “Assert” operator will raise an error if a matching row is not found.
|--Clustered Index Insert(employee)
|--Clustered Index Scan(department)
Unfortunately, this technique is restricted to only query (vs. table) level hints, so it's not possible for example to force the indexes being used when accessing the other table involved in the constraint (department in the example). Also, TSQL syntax does not allow specifying query level hints for scalar inserts, like "insert table values...", but the easy workaround is to rewrite the statement as "insert select" like in the example. Update and delete statements do regularly accept query level hints.
update employee set empname = 'Conor', deptid = 2 where empid = 1 option (loop join)
|--Clustered Index Update(employee)
|--Clustered Index Seek(department)
Let us now look at slightly more complex example with an indexed view.
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
create table t1(i int, j int)
create table t2(h int, k int)
create view v with schemabinding as
select i, h, count_big(*) c from dbo.t1, dbo.t2
where j = k
group by i, h
create unique clustered index v_ih on v(i, h)
The changes to either table t1 or t2 need to be propagated to the indexed view v1, in order to keep it consistent at all times. Since the indexed view contains a join and an aggregation in its definition, the Optimizer will automatically augment DML query plans against t1 or t2 with joins and aggregations. Query level hints can be used to influence the join and/or grouping strategy employed by the Optimizer in the query plan.
insert into t1 select 1, 2 option (hash join, hash group)
| |--Table Insert(t1)
|--Clustered Index Update(v)
|--Hash Match(Right Outer Join)
|--Clustered Index Scan(v)
|--Hash Match(Inner Join)
insert into t1 select 1, 2 option (loop join, order group)
|--Nested Loops(Left Outer Join)
| |--Nested Loops(Inner Join)
| |--Table Spool
| |--Table Scan(t2)
|--Clustered Index Seek(v)
Needless to say, query hints are fully documented in Books Online – look for the “Query Hint (Transact-SQL)” topic.