By default you’ll get up to 5,000 records that match your query. The top-level <fetch> element supports a few options to help you control this:

top will limit the number of records further, e.g.

<fetch top="10">
  <entity name="account">
    <all-attributes />
  </entity>
</fetch>

When you use top to limit the number of records, you don’t have the option to move on to subsequent pages. You can’t use this to increase the number of records beyond 5,000 – you can only use a number between 1 and 5,000.

In contrast, count and page allow you to move through multiple pages of results and control the size of each page:

<fetch count="100" page="3">
  <entity name="account">
    <all-attributes />
    <order attribute="createdon" />
  </entity>
</fetch>

This example will get records 201 – 300. If you are working you way through sequential pages you should also set the paging-cookie attribute with the value of the paging cookie from the previous page.

So when should you use top and when should you use count and page?

If you are only ever interested in the first n records (e.g. you just want the first record that matches your query), use top as you have no need to retrieve multiple result pages.

On the other hand, if you do need to retrieve all the results (or at least, more than “just” the first 5,000), use page to indicate which number page you want. In addition you can optionally use count to control the size of each page – you might set this after some trial-and-error testing of the performance of your query.

SQL Equivalents

In SQL terms, top is equivalent to the SELECT TOP (n) clause. count and page are similar to the OFFSET ... FETCH ... clause.

2 thoughts on “Inside FetchXML pt 8 – page, count & top”

  1. I’m getting odd behavior from “top,” when there are child records and the sort is on those child records. We had a requirement that wanted us to pull the “oldest” record out of queue — but not by the queue entry date, but based on a date in the target record. We created a fetch query that joined from queue to queueitem to the target entity. We then sort by fields on the target entity, and the “top” clause is just ignored, we get all the records in the queue. “Count” works as expected for this purpose, but it seems like it’s a bug.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.