Database Reference
In-Depth Information
Figure 7-6.
A variation of the data retrieval mechanism, a scan
In the properties in Figure
7-6
, there is no seek predicate. Because of the function being performed on the column,
the
ISNULL
and the
CONVERT_IMPLICIT
, the entire table must be checked for the existence of the
Predicate
value.
isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].[Sales].[SalesOrderDetail].
[UnitPrice] as [sod].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2012].
[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sod].[UnitPriceDiscount],0))*CONVERT_I
MPLICIT(numeric(5,0),[AdventureWorks2012].[Sales].[SalesOrderDetail].[OrderQty] as [sod].
[OrderQty],0),(0.000000))>(20000.000000)
Because a calculation is being performed on the data, the index doesn't store the results of the calculation, so
instead of simply looking information up on the index, you have to scan the data, perform the calculation, and then
check that the data is correct.
Analyzing Join Effectiveness
In addition to analyzing the indexes used, you should examine the effectiveness of join strategies decided by the
optimizer. SQL Server uses three types of joins.
•
Hash joins
•
Merge joins
•
Nested loop joins
