Beware, though, if the possible cluster key is a monotonically sequential, or serial key—for example, an integer whose values always go up and always go up by the same amount, when you INSERT. The problem with monotonics (which are sometimes hidden behind names like "identity" or "timestamp" columns—see Chapter 7, "Columns") is that the nth row and the (n + 1)th row will both fit in the same page—so there might be contention for the same resource. Usually, in multiuser systems, you want rows to be dispersed.
This above all—The benefits of clustered indexing depend on a monster assumption; namely, that you want to retrieve and sort by the cluster key far, far more often than with any other key.
Comments
Could somebody explain this?
Also navision uses server sided cursors, and reading about cursors,
I know navision has default values, but does copy them to sql default values?
The developer should control how information is presented in the application. The order of fields in the table becomes irrelevant to the application user. Also avoid the use of select *.
1) Don't use it, not even in pure SQL. It may be fast, but the dangers are too high.
2) Some comparison : Let's take some street address. At a certain moment of time a certain person lives there. And you can use it to point to that person. If that person leaves the house (=DELETE) and another person moves into the house (=INSERT), the address is still the same, BUT the person has changed and it points to the wrong person (=record). In this case you should have used the persons name (for this example I am considering the persons name is unique!).
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
So does this mean Clustered index shouldn't be the entry no. but
for example Item No., Entry No. and have 99 % fill rate so that new entries get filled inbetween?
thanks.[/quote]
Hi NavStudent,
The answer to your questions is: "Depends"
Like always you have to balance between transaction speed and reporting speed.
On Item Ledger Entry let's say would seem to make logical sense to have say Item No., Posting Date as your clustered index. Reads would be so much faster.
However since clustered index is how the data is actually stored on the disk when you do an insert the system will have to insert somewhere in the middle of the table which will lead to much higher insert time.
If your primary key is Entry No. the inserts always happen at the end of the table so they will be very fast.
So depending which one you do a lot more often it may make sense to change it or not.
I actually changed it for Item Ledger Entry and Value Entry in an attempt to speed up Adjust Cost routine but since I have a lot of inserts happening I had to revert back to Entry No. (I think it was something like 5 ms for Entry No. and 110 ms + for Item No., Valuation Date for Value Entry).
Hope that helps a little.
The developer should control how information is presented in the application. The order of fields in the table becomes irrelevant to the application user. Also avoid the use of select *.[/quote]
I believe the suggestion to "avoid use of Select *" should be directed to MS
Isn't that what time stamp is for?
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!