Hello I ran the following code in query analysizer on sql to study it.
dbcc show_statistics ([Jason Pharm Test$Sales Shipment Header],[$1])
The table is Sales SHipment Header.
The key in navision is Order No., No.
The density is .9997
There are 42696 records in the table.
The following sql statement took 3130 ms.
SELECT * FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header" WHERE (("Order No_" LIKE @P1)) ORDER BY "No_" OPTION (FAST 10)
Compared to other sql statement is is taking 3 seconds. Which is hight.
I looked at execution plan and it uses clusterd key 82% compared to
secondary key (Order No., No.). Why is sql doing this?
Even if I change the Order by to "Order No_" it still uses clustered index.
SELECT * FROM "NAVPROD"."dbo"."Jason$Sales Shipment Header" WHERE (("Order No_" LIKE 'SO7511273')) ORDER BY "Order No_" OPTION (FAST 10)