>It won't do a sort if the field is indexed. The where clause will force the
>server to go straight to the smaller subset of records you want, then sort
>this subset only. The masterid field is a foreign key - if you've defined it
>as such it will already have an index - don't create another one - this may
>slow things dow if you do.

The masterid field is not under foreign key constraints because this caused bulk addition of records to be very slow: removal of the foreign key constraints, among other improvements, brought my insert speeds from the low tens of records per second to just under a thousand per second. So I removed the foreign key constraint and added an index, which I disabled during the initial bulk data-insertion operation, and then re-enabled afterwards. Now that we are just doing the small hourly insertion runs, not involving more than a few thousand records per hour, I just leave the indices all enabled.

I understood that it would only sort the subset of detail records that match the masterid field value. However, this small subset is currently tens of thousands of records and one day will likely be somewhere in the neighborhood of a hundred thousand records. If I need to select every row from the master table, will I not then be doing separate subset sorts for each master row? Will those sorts not each be very slow?

>No order by clause will sooner or later deliver unexpected results - no
>question of that - no matter how you think the records are added. Next time
>you backup and restore, the records may not be in the same natural order.

It seems to me that this statement may make my speed concerns moot, unless I can find an alternative solution. If the data may get out of my definition of natural order, then I indeed have a problem. I wonder if a better solution might not be to change my index, so that the "masterid" index is based upon both the "masterid" and the "date" columns? Actually, that index is currently a two-column index: masterid and id (primary key). I am thinking I should make it be masterid, date, primary key. As I don't know for certain what execution plan the SP actually uses, however, I can't know if the index is being used. Hmmm, I guess I could just try it against the big database, and let the execution time tell me whether the index was used or not <grin>.

Thanks for all your help!

Dan.