>>>>>I also have to admit to a change I needed to make. I used to accept the default of clustering on the primary key. It seems the only need for a clustered key is when you absolutely need a set of records returned in a cluster, such as all the lineitems for a given invoice. The invoice foreign key should be clustered on the line items table, not the primary key on either invoice nor lineitems. Right?
>>>>
>>>>That is better if you're doing a lot of (demanding) queries on them... correct. There might be nuances to this rule, but in general I'd say that is correct.
>>>
>>>I'd rather have these clustered by item code. By invoice, you generally need only when you're totaling and printing it, which is just about once or twice per document. Calculating and reporting, which may be done monthly and/or annually or even several times a day (some apps require recalculating stock on every change) requires access to records of the same item code.
>>>
>>>IOW, yes, cluster on the most frequently used order - just saying that the document number is not the most likely candidate for it.
>>
>>Indeed, it depends on the queries you want to optimize the most. Whether that is an invoiceno, invoicedate, itemcode or something else, is a decision that has to be made by the developer or DBA
>
>And I'd like to be more precise in what I meant, why document number is a bad choice. You need it only when editing/saving a document, and when printing it out. And that's when you don't really need the speed. Even the slowest method will still be faster than the printer and far faster than the data entry. And whatever slowness there may be, it's experienced by one user, the one manipulating the document, and they are well aware that saving it may take some time. Even office type software may take its time to save one document, even when it's a single file (well, seemingly - it's about 20 xml files in subfolders, zipped). However, about every user working with line items will like to have the line items available immediately - be it just viewing the history of an item (purchases, sales, stock) or waiting to pull the totals for a period (which used to be immediate with dbfs 20 years ago - don't see why it wouldn't be today).
>
>The definition of "most frequently used order" needs also to take not only the number of times it is used, but also the number of records read, IOW the total I/O.

I agree. Even if the document primary key is clustered, and even if the UI is a list of records which the user selects one in order to download the entire record, the chance that they will access the records in primary key order are slim.