You're absolutely right! It's all a matter of balance and "If this then that or maybe not" - that's why I said "test thoroughly". But have in mind that your concerns basically affect 99% of all indexes in NAV, thus changing the CI on T32 is not such an unsusal thing.
I don't think that it's a problem to have Entry No. as the CI, but I think it's in general the best CI you can have (unique, narrow, static, ever-increasing) (if they change it to auto-increment it would be much better, but that's another story).
Indeed it IS a problem which affects most of the LE tables! Item Ledger Entry, G/L Entry, Customer Ledger Entry, etc. they are all affected by the same problem - and from a physical perspective it is a problem, which is responsible for many index scans (or other stupid QEP). "Entry No." is actually the worst CI you could have - true, it has the highest selectivity - but data is allmost never queried on that field. The primary criteria
for querying data is - I daresay in 99% of all cases - something with "Item No.", thus arranging the data on basis of this "primary query criteria" is a smart idea! And besides solving some I/O issues this has also tremendous impact on some blocking situation, as in SERIALIZED transactions (LOCKTABLE) usually range-locks are established. The wider that range, the more blocking potential ...
Generally: as you said, having the CI on the PK is mostly a smart idea, but in some cases other considerations may be done ... as so often: it depends ...
1. If you change to Item No., Entry No. both fields have to be duplicated in every secondary index (CI is part of every sec. index). So every sec. index will use more disc/ram space and produces more I/O when reading/writing that index.
True. But actually we are talking about "peanuts" here ... In NAV there are far worse indexes ...
2. Page splits in CI will occur. With just Entry No. as CI we have an ever increasing field as CI. So we will don't see page splits and we don't need to reindx the CI (Item Leder Entries will rarely be deleted). With Item No., Entry No. there will be many page splits and we have to reindex a lot. Item Ledger Entry is often one of the biggest tables in our DBs and reindex takes a lot of time. And if you don't have a SQL Server Enterprise Edition you have to reindex offline.
Again, also "Page Splitting" is daily business. Actually that's just a matter of adjusting the approriate Index-Fillfactor (if you have tools that could calculate this optimizerd FF
) and the method you use to defragment/reindex (IMHO: the standard features suck, that's why I use optimized utilities).
While a "Entry No." CI needs a FF of 100%, with changing you need something below, hence, the index will grow. But then again: size does not
matter. Technically larger indexes require more effort in updating and maintaining them, but we're talking about microseconds here - something which might be acceptable compared to the potential benefit regarding read-performance and blocking.
So if indeed the write-performance/"experience" is affected strongly depends on the underlying hardware resources.
3. I don't see the scanning problem. We have secondary indexes that can be seeked for Item No..
This also depends. The problem actually occurs with Items that are daramatically more often posted than others, thus if the ratio of a certain "Item No." is remarkably greater than others, as this actually decreases the "selectivity" of that field ...
If you have a problematic query on "Item No." indeed you'll mostly see the QEP performing "Index Seeks" (rarely a "Scan") but causing thousands of "Reads" as the "Key Lookup" operation is causing most of the effert ...
We only avoid bookmark lookups, when filtering for Item No.
If we filter for Item No. plus some other fileds chances are good that SQL Server will use a secondary index plus bookmark lookups.
How do you avoid "Bookmark Lookups"? If NAV is firing a SELECT * you'll always have a key lookup, except if your index is covering - means in this case: it INCLUDES all (!) other fields ... Creating such an index on T32 is something a absolutely would NOT recommend ...
There maybe situations where all those overhead is justified, but those are very rare.
Not so rare in my experience - and I deal with this every day.
To sum up:
Changing a Clustered Index is something which needs to be discussed within context of the current problem
. There are advantages and disadvantages which have to be compared (I guess Tobias and I gave an example for such a discussion) - what's a solution for one system might be a problem for another; with NAV/SQL performance there's hardly something like "IF THIS HAPPENS THEN DO THAT", it's mostly "IT DEPENDS" ...
Thus, having all this in mind: just test to find out if this could help you!