Using Dynamic Index

This statement indicates that the query optimizer has built its own index during the execution of the query, for use in its OR strategy. Because queries involving OR clauses are generally not very efficient in terms of quickly accessing the data, the SQL Server optimizer might use the OR strategy. When the OR strategy is used, the optimizer makes several passes through the table ¾ one pass for each argument to each OR clause. The results of each pass are added to a single worktable, and the worktable is then sorted to remove any duplicate rows.

The worktable does not contain the actual data rows from the table; rather, it contains the row IDs for the matching rows. The row IDs are simply a combination of the page number and row number on that page for each of the rows.

When the duplicates have been eliminated, the optimizer considers the worktable of row IDs to be, essentially, its own index (Dynamic Index) pointing to the table's data rows. It can then simply scan through the worktable, get each row ID, and return the data row from the table that has that row ID.

The OR strategy is not limited to queries that contain OR clauses. When an IN clause is used to list a group of possible values, SQL Server interprets this as though the query had a separate equality clause for each of the values in the IN clause.

To illustrate the OR strategy and the use of the Dynamic Index, the following queries are based on a table with 10,000 unique data rows, a unique nonclustered index on column col1, and a unique nonclustered index on column col2.

Query 1:

SELECT *
FROM Mytable
WHERE col1 = 355
OR col2 = 732

SHOWPLAN 1:

STEP 1
The type of query is SELECT
FROM TABLE
Mytable
Nested iteration
Index : col1_idx
FROM TABLE
Mytable
Nested iteration
Index : col2_idx
FROM TABLE
Mytable
Nested iteration
Using Dynamic Index

Query 2:

SELECT *
FROM Mytable
WHERE col1 IN (700, 1503, 311)

SHOWPLAN 2:

STEP 1
The type of query is SELECT
FROM TABLE
Mytable
Nested iteration
Index : col1_idx
FROM TABLE
Mytable
Nested iteration
Index : col1_idx
FROM TABLE
Mytable
Nested iteration
Index : col1_idx
FROM TABLE
Mytable
Nested iteration
Using Dynamic Index

SQL Server does not always resort to using the OR strategy for every query that contains OR clauses. The following conditions must be met before SQL Server uses the OR strategy:

• All columns in the OR clause must belong to the same table.
• If any portion of the OR clause requires a table scan (due to lack of index or poor selectivity of a given index), a table scan will be used for the entire query, rather than the OR strategy.
• The decision to use the OR strategy is made after all indexes and costs are evaluated. If any other access plan is less costly (in terms of page I/Os), SQL Server uses the plan with the least cost. In the examples, if a straight table scan would result in less page I/Os than using the OR strategy, the queries would be processed as a table scan instead of using the Dynamic Index.