BlogmyQuery – BMQ

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.

use tempdb

go

create table department(deptid int primary key clustered, deptname varchar(10))

go

create table employee(empid int primary key clustered, empname varchar(10), deptid int references department(deptid))

go

insert department values(1, 'Optimizer')

go

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.

|--Assert

|--Merge Join

|--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)

|--Assert

|--Nested Loops

|--Clustered Index Update(employee)

|--Clustered Index Seek(department)

Let us now look at slightly more complex example with an indexed view.

use tempdb

go

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

go

create table t1(i int, j int)

go

create table t2(h int, k int)

go

create view v with schemabinding as

select i, h, count_big(*) c from dbo.t1, dbo.t2

where j = k

group by i, h

go

create unique clustered index v_ih on v(i, h)

go

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)

|--Sequence

|--Table Spool

| |--Table Insert(t1)

|--Clustered Index Update(v)

|--Collapse

|--Sort

|--Compute Scalar

|--Hash Match(Right Outer Join)

|--Clustered Index Scan(v)

|--Hash Match(Aggregate)

|--Hash Match(Inner Join)

|--Table Spool

|--Table Scan(t2)

insert into t1 select 1, 2 option (loop join, order group)

|--Sequence

|--Table Spool

| |--Table Insert(t1)

|--Clustered Index Update(v)

|--Collapse

|--Sort

|--Compute Scalar

|--Nested Loops(Left Outer Join)

|--Stream Aggregate

| |--Sort

| |--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.

Ciao,

Stefano