When a query joins columns of numeric or decimal datatypes, an index can be used when both of these conditions are true:
The scale of the column being considered for a join equals or exceeds the scale of the other join column, and
The length of the integer portion of the column equals or exceeds the length of the other column’s integer portion.
Here are some examples of when indexes can be considered:
Datatypes in the join |
Indexes considered |
|---|---|
numeric(12,4) and numeric(16,4) |
Index considered only for numeric(16,4), the integer portion of numeric(12,4) is smaller. |
numeric(12,4) and numeric(12,8) |
Neither index is considered, integer portion is smaller for numeric(12,8) and scale is smaller for numeric(12,4). |
numeric(12,4) and numeric(12,4) |
Both indexes are considered. |
