Database Reference
In-Depth Information
Figure 25-8.
The predicate of the clustered index scan
This is a calculation. There is an existing index on the
VendorID
column of the
PurchaseOrderTable
that might
be of use to this query, but because you're using a
COALESCE
statement to filter values, a scan of the entire table is
necessary to retrieve the information. The
COALESCE
operator is basically a way to take into account that a given value
might be
NULL
and, if it is
NULL
, to provide an alternate value, possibly several alternate values. However, it's a function,
and a function against a column within a
WHERE
clause, the
JOIN
criteria, or a
HAVING
clause is likely to lead to scans, so
you need to get rid of the function. Because of this function, you can't simply add or modify the index because you'd
still end up with a scan. You could try rewriting the query with an
OR
clause like this:
...WHERE per.LastName LIKE @LastName AND
poh.VendorID = @VendorID
OR poh.VendorID = poh.VendorID...
But logically, that's not the same as the
COALESCE
operation. Instead, it's substituting one part of the
WHERE
clause
for another, not just using the
OR
construct. So, you could rewrite the entire stored procedure definition like this:
ALTER PROCEDURE dbo.PurchaseOrderBySalesPersonName
@LastName NVARCHAR(50),
@VendorID INT = NULL
AS
IF @VendorID IS NULL
BEGIN
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.LineTotal,
p.[Name] AS ProductName,
e.JobTitle,
per.LastName + ', ' + per.FirstName AS SalesPerson,
poh.VendorID
