SharePoint Why Should Indexing a SharePoint Field Break Your CAML Query?

This is what our original query looked like:

      <Where>
      <Eq>
      <FieldRef Name='Project' LookupId='TRUE'/>
      <Value Type='Lookup'>100</Value></Eq>
      </Where>
      

It’s not a complicated query, and this used to work. Used to. But for some reason, it doesn’t anymore.

And for some reason, if we change the Value from Type=’Lookup’ to Type=’Integer’ it magically works again:

      <Where>
      <Eq>
      <FieldRef Name='Project' LookupId='TRUE'/>
      <Value Type='Integer'>100</Value></Eq>
      </Where>
      

Why will using Type=’Lookup’ suddenly fail, and using Type=’Integer’ work? Does this mean we need to change all of our CAML queries that use Lookup?

What changed? No new service packs. No recent patches.

We tried to retrace our steps, in the hope that we don’t have to rewrite all of our CAML queries and retest all our application pages:

1. No change
2. No change
3. Ah, one minor change, but all we did was create an index in the Project field because we have over 2000 items in this list.

Indexing the column should help optimize retrieving records from this list. But could indexing the Project field be the culprit? This seems to be about the only change we did on this list.

But I think to myself, it should not be the culprit. Right?

I am a database person, and I create indexes here, there and everywhere whenever I need performance gains on some of my reports (ok, let me qualify that, I index where it makes sense. Of course if it is a purely reporting read only database, then I will index the heck out of it. OLTP databases are a different story). So for me, I don’t think it should affect the way we retrieve our records using our CAML query.

We needed to test if this was the case, so we did a quick experiment:

1. Created a custom list with a field that uses a regular lookup to another list
2. Used CAML with Type=Lookup ——-> Works
3. Indexed the column
4. Re-tried the CAML with Type=Lookup ——-> Does not work
5. Tried CAML with Type=Integer ——-> Works
6. Removed index from column
7. Re-tried the CAML with Type=Lookup ——-> Magically works again

So, why would (or should) the implementation of our CAML query change when we decide to index, or drop an index, from one of our SharePoint lists?

We tried to look for an explanation, but we haven’t found any official documentation, or any definitive rationale.

Again, maybe I just don’t understand, but in my database world, creating indexes, or dropping indexes from my tables does not break my SQL queries.

So why should indexing break a query in the SharePoint world?

Anyone from Microsoft SharePoint team care to explain? I’m sure we’re not the only ones interested to know the answer.