Tip #738: Use top in fetchxml

Tip top enrichedI’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

<fetch count="5">

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.

Tweet about this on TwitterShare on Facebook0Share on Google+0

Leave a Reply

Your email address will not be published. Required fields are marked *