I’ll be first to admit: I was not aware that fetchxml supports top attribute (as it turns out, I’m not the only one hence the tip). It does not seem to be documented anywhere but, according to the schema, has been around since CRM 2011.
Consider this fetchxml
<fetch top="5" > <entity name="contact" > <attribute name="contactid" /> <attribute name="fullname" /> </entity> </fetch>
It will emit the following SQL:
select top 5 "contact0".ContactId as "contactid", "contact0".FullName as "fullname" from ContactBase as "contact0"
And the output will be very concise, without any paging cookies and with morerecords=”0″ indicator.
Modify fetchxml to use count
And sql becomes
select top 6 "contact0".ContactId as "contactid", "contact0".FullName as "fullname" from ContactBase as "contact0" order by "contact0".ContactId asc
See? Count implies pagination and, to provide one, output needs to be sorted and one extra record is required (to be a marker for the next page). (Generally speaking, restricting results without a sort order is not very useful anywhere except testing but it’s a discussion for another day).
The difference is subtle but important: top means “give me X records and that’s it”, while page/count combination tells CRM “give me page Y of the data where page size is X records [and tell me how to get to the next page]”.
I would recommend using top if all you care about is a fixed top number of records (e.g. five most profitable accounts) and page/count when you do need to paginate over the resultset.