After launching our SQL2FetchXML website, we received a few inquiries about some special SQL scripts that cannot be converted to FetchXML queries using the free online utility. I here put together a list of the limitations with CRM FetchXML that I have observed. I will try to be thinking of the limitations by comparing it to what can be done through SQL script.
- RIGHT OUTER JOIN is not supported.
You can't compare two fields directly, you can only compare a field with a static value.(This is no longer true as of July 2020 if you are on the latest Dynamics 365 online version or future on-premises releases as made available)You can't have OR condition across entities.(This is no longer true since CRM 2013 release)- You can't use SQL functions in FetchXML query. CRM has support for some built-in functions, but any additional SQL functions are not supported.
- No support of UNIONs (Thanks to Dirk Fabricius for his contribution by commenting on this post initially).
- There is no support of a CASE / WHEN structure.
- When you issue a FetchXML query, the maximum number of records you get back from CRM server is 5,000 each time. If you want to get more records from CRM server, you would have to use paging cookie.
- You can't have more than 10 linked entities in a FetchXML query. It is possible to overcome this limit by creating or updating a QueryLinkEntityLimit setting, however this is generally not recommended. If you ever run into this situation, you would definitely want to re-visit your CRM data model or re-engineer your query.
- When you perform an aggregation, the maximum number that will participate in the aggregation will be 50,000 records. For instance, if you do a COUNT aggregation, the maximum value you can get back from CRM is 50,000 even though that you might have more records in the system. This is a by-design behavior which is for performance reason. This can be overcome by updating "AggregateQueryRecordLimit" setting, however it is generally not recommended.
There is no way to use subqueries.(This is no longer true after some recent updates in 2024, you can now use EXISTS, IN, ANY, NOTANY, ALL query conditions).- There is a wacky limitation that you can't have more than 2097 conditions per filter, according to Daniel Halan.
This is what I have got so far. Please let me know if I have missed anything.