Today I had a problem with a table that has around 5 million records on my SQL Server 2012. The table has as primary key “Header No.”,”Transaction No.”. In the table is a field “Order No.” that at the moment is blank for all records. I also had an index on that field, because I will need to search for it. I think that in the future only 1 in several 1000 records will have an order no. So the index will be highly selective because I will have only 1 record per order no.

The code is like this:
Table.RESET;
Table.SETCURRENTKEY(“Order No.”);
Table.SETRANGE(“Order No.”,TheOrderNo);
IF Table.FINDFIRST THEN BEGIN

It should go lightening fast but it takes almost 1 second for each FINDFIRST.

Why?
The SQL plan gave me this:

Top[2,1];Nested Loops[3,2];Index Seek($2)[4,3];Clustered Index Seek(Company Name$Table$0)[6,3]
WHAT? A CLUSTERED index Seek?????
it should have been an index seek.
The select was what I expected:
SELECT TOP 1 * FROM “Company Name$Table” WITH (READUNCOMMITTED)  
HERE ((”Order No_”=?)) ORDER BY “Order No_”,”Header No_”,”Transaction No_” OPTION (OPTIMIZE FOR UNKNOWN)

But because SQL knows that the Field “Order No.” contained only blanks, it decided to do a clustered index seek because of the ORDER BY (at least this is what I think SQL decided). So how to fix it NOW (I don’t want to wait that it goes into production and it takes days or months before the problem fixes itself.

What is the code for?
The FINDFIRST serves because I want the Invoice No. of that record and it has to be a non-blank value.
So, I changed the index from “Order No.” to “Order No.”,”Invoice No.”. It makes the index a little bigger, but it shouldn’t be a big problem (or at least I hope so).
The C/AL code I changed like this:

Table.RESET;
Table.SETCURRENTKEY(“Order No.”,”Invoice No.”);”
Table.SETRANGE(“Order No.”,TheOrderNo);
Table.SETFILTER(“Invoice No.”,’<>%1’,’’);
IF Table.FINDFIRST THEN BEGIN

Even with all blank values in “Order No.”,”Invoice No.” at the moment, SQL has changed its plan to an index seek and the code has become very fast.