Hey all, I just wanted to point out a sneaky little problem we ran into today at work. I’ll outline it briefly here, but Donabel wrote a great summary that we’ve posted on the Black Ninja blog.
The problem to be aware of is related to SPQuery objects that have the LookupId set to TRUE and the Value Type set to Lookup. An example:
1 2 3 4
SPQuery oQuery = new SPQuery(); oQuery.Query = "<Where><Eq><FieldRef Name='Employee' LookupId='TRUE'/>" + "<Value Type='Lookup'>161</Value></Eq></Where>";
With a NON indexed column, the above query works fine. Once you change the Employee field so it is an indexed column, the above query will fail to work. Changing Value Type from Lookup to Integer seems to make it work again. So somehow, something is changing when a column is indexed thus causing the query to behave strangely.
Removing the index on that column makes everything work magically again. Thanks to the following post for affirming what we had already painfully discovered.