Understood on the test data. We can fix that for testing purposes but let's hold off on that and the indexes for a minute because, other than the join columns, there doesn't appear to be any criteria that would prevent a full table scan (full clustered index scan) other than the HasProcessed column of the #TestDataDump column. Here's the code that's doing the work for you according to the code you attached (everything else is setting up test data).

insert into #TestProcessedData(IdCard,ProductName,ProductCustScore,CardNumber,CutomerName,CustomerId)

SELECT CC.IdCard,CP.ProductName,CPS.ProductCustScore,CC.CardNumber,C.CustomerName,C.CustomerID

from #TestDataDump TD left join #CrediCard CC on(TD.CardNumber = CC.CardNumber)

left join #Customers C on C.CustomerID = CC.CustomerId

LEFT join #CustomerProduct CP on TD.ProductNumber = CP.ProductNumber

LEFT join #CustomerProductScore CPS on(CP.IdCustProduct = CPS.IdCustProduct)

[font="Arial Black"]Where TD.HasProcessed = 0;

[/font]

select * from #TestProcessedData;

Here are my questions (and you may have already stated answers for some of these but wanted to try to get them all in one spot)...

1. Does the #TestDataDump table represent all new data or is this maintained in a real table that you keep adding to? If you keep adding to it, how often and how many rows are typically added?

2. Are there duplicates in the real table represented by #TestDataDump table insofar as the CardNumber and and ProductNumber?

3. What percentage of rows will have HasProcessed = 0 in the table represented by #TestDataDump?

4. You construct the ProductNumber in your test data using N+1000, which is 100% numeric. Is the ProductNumber totally numeric or does it have non-numeric characters in it or any leading zeros?