Simple Question SQL Index

Belias · 2010-06-21

Nav 2009sp1, sql2005
hi everyone, i think this is a very simple question, i think: i want to create a new key on 2 fields, that are both option type --> not selective.
There are no sumindexes on this index and i wanted to set the key like this:
Key: OptionField1,Optionfield2
SQLIndex: Primarykeyfield,OptionField1,OptionField2 (the default index would be OptionField1,OptionField2,Primarykeyfield)

Is this modification effective?or is it pointless?is it risky? (i know about some issues about sqlindex property)

kriki · 2010-06-21

A new index is only useful if your filters retain few records (5% or less of the total records in the table), otherwise SQL will NOT use the index and keep using the clustered index.
An example:
T21:"Customer Ledger Entry" with 1.000.000 records
A filter on "Open" in is not useful if you want all the closed records (probably 990.000 records).
But if you want all "Open" records (about 10.000), the filter can be useful (but it is not guaranteed).

So if you think it might be useful, create it and see if SQL really uses it.

David_Singleton · 2010-06-21

Well really it depends on the version. In the newer versions, SQL really has no choice of what index to use, it works the same as Native, you tell SQL what to use.

So if you plan to filter on these fields and its a big table then you need the indexes irrespective of how selective they may be.

Belias · 2010-06-21

David Singleton wrote:

Well really it depends on the version. In the newer versions, SQL really has no choice of what index to use, it works the same as Native, you tell SQL what to use.

So if you plan to filter on these fields and its a big table then you need the indexes irrespective of how selective they may be.

really? i tried to do a client monitor on my query (which is a dataitem of a report with sourcetableview as my new index, with filters according the index) and nav actually does a clustered idx seek...which is not the new index, obviously... :-k
i also thought that nav (SQL) would have follow my instruction, but it didn't...

DenSter · 2010-06-21

Did you use that new key in a SETCURRENTKEY statement?

Belias · 2010-06-21

No, because as i said, there is the key already set in the sourcetableview of the dataitem...Don't tell me that i have to explicitly use SETCURRENTKEY :shock: [-o<

DenSter · 2010-06-21

As long as the ORDER BY clause matches the index exactly it should be forced to use the index. If the ORDER BY clause doesn't match exactly, it often reverts to a clustered index scan.

David_Singleton · 2010-06-21

I think the person that made these Dynamic Cursors changes in Navision was reading the Harry Potter books whilst coming up with this solution.

Belias · 2010-06-22

I couldn't post this yesterday, (no connection) sorry...
My sql plan said Clustered index seek, not scan...i have to retry the test, maybe there was some filter on the primari key, too... :-k

Simple Question SQL Index

Answers

Categories