Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan.

The following are the queries (as per the query plan, #1 does not use indexes, #2 does)-

UPDATE Payment_Metadata 
    SET 
    Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
    Payment_Metadata.C1 = 'RAW MATERIALS', 
    Payment_Metadata.C2 = 'INGREDIENTS', 
    Payment_Metadata.C3 = 'OTHER ', 
    Payment_Metadata.RuleText = '---', 
    Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
    Payment_Metadata.IsExcluded = 0, 
    Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
    FROM 
    Payment_Metadata 
    WHERE 
    Payment_Metadata.IsProcessed = 0 
    AND (Payment_Metadata.EnrichedVendor = 'NFL' 
         OR Payment_Metadata.Vendor_No = 'NFL')

The second query uses an index:

UPDATE Payment_Metadata 
SET 
Payment_Metadata.CommodityCode = 'RAW MATERIALS', 
Payment_Metadata.C1 = 'RAW MATERIALS', 
Payment_Metadata.C2 = 'INGREDIENTS', 
Payment_Metadata.C3 = 'OTHER ', 
Payment_Metadata.RuleText = '---', 
Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, 
Payment_Metadata.IsExcluded = 0, 
Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' 
FROM 
Payment_Metadata 
WHERE Payment_Metadata.IsProcessed = 0 
  AND (Payment_Metadata.EnrichedVendor = '0202054' OR  
        Payment_Metadata.Vendor_No = '0202054')

The following is the table definition:

```
CREATE TABLE [dbo].Payment_Metadata NOT NULL,
[Company_Code] varchar NULL,
[Comp_Code_Desc] varchar NULL,
[Vendor_Acct_Group] varchar NULL,
[Vendor_No] varchar NULL,
[Vendor_Name] varchar NULL,
[Vendor_ABN] varchar NULL,
[Vendor_PTerm] varchar NULL,
[Vendor_PTerm_Desc] varchar NULL,
[Purchasing_Group] [v

SQL Server uses statistics to determine an execution plan. If an index is available, so are statistics, and SQL server will determine the path of least work. This could be using the index or doing a table scan.

In your example SQL server has determined that a table scan is less work than doing an index seek and a bookmark lookup.

What you can see is that one of your querys is "less selective", ie it probably updates more records, meaning that more bookmark lookups are needed to satisfy the second query. SQL server just estimated that for that query the sum of index seeks + bookmark lookups are more work than simply doing a table scan.

Since SQL server reads entire pages, and not records, your query (as indicated by statistics) needs to be selective enough for the index to be used, if SQL server estimates that it's going to have to read every page anyway it will go for a table scan.

See here and here for a more indepth explanation with sample data and results

StackExchange Database Administrators Q#97781, answer score: 5