It's important to be careful---guess what this SQL execution means

Today, when we help the customer to do the sentence optimization, suddenly encountered such a statement, similar to the following example (the original statement is an update)

The example uses two tables from the AdventureWorks data.

ProductID is [Production]. The primary key of the [Product] table, in [Purchasing]. Nonclustered indexes are also established in [PurchaseOrderDetail]

SelectName, (Select Count(*) from [Purchasing].[PurchaseOrderDetail] whereProductID=ProductID) from [Production].[Product]whereProductID= 341

　　

-----------------------------------here, what kind of index do you choose to recite the meaning of the statement in mind? -----------------------------------------

　　　　

First, take a look at the execution plan

　　　　

[Purchasing]. [PurchaseOrderDetail] Why go index scan? This obviously should be indexed to find Ah!

Take a closer look at the predicate of the index scan

　　　　

Did I match myself to myself? Is it the same as you think?

Modify the code to see where the problem lies

　　　　

 select  name, (select  count  (* ) from  [ purchasing  ] . [ purchaseorderdetail   " 
 
 where  ProductID =  [Production]. [Product].  ProductID)  

 from [Production]. [Product] where = 341

　　　　

　　　　

This is a mistake due to carelessness, the example is a query, in fact, it is an update effect can be imagined, and this effect in the stored process has not been discovered for a long while, but if the optimization to view the execution plan relationship overhead, I take a look at the code also assume that absolutely no problem!

Solve the problem, that is, the problem of slow-running statements, and find out the program bug.

Summary: careful is important!

It's important to be careful---guess what this SQL execution means