Change of SQLIndex property

fufikk

Hi,

I've been doing some reading on changing the SQLIndex property to achieve better index selectivity in SQL. My particular interest is in T355 - Ledger Entry Dimension. I do wonder what would happen if the PK would start with "Entry No." instead of "Table ID" (in the SQLIndex). Unfortunatelly, I do not have a test database at the moment that would have enough data in that table to observe any positive or negative impact of that action.

So I figured, I ask here if anyone has any experience with that. Any replies are appreciated

kriki

The selectivity will be better. Microsoft did it with Table 37 at a certain time. They created an extra index with "Document No.","Document Type","Line No." if I remember correctly.

Depending on the number of data it will take some time to change the SQLIndex property because it will rebuild the clustered index.

It will probably be more performant. But as always: before putting something in production, test it to see if it is more performant.

PS: For performance: don't create a secondary key with the new key. It will slow down insert performance a lot for only helping a little the reading performance.

Also keep the fillfactor of the table at max 90% (this is not a 'hard' rule but just a number [educated guess!] to start with).