SharePoint List Throttling & Indexed Columns

Recently I had worked on an issue related to SharePoint List Throttling. Customer has a SharePoint list which has total 7000 items and the throttling limit was set to default 5000. They were using the lists.asmx web service from a custom application remotely. It was using the a CAML query like below to filter based out a column called “Employee Number” and pulling only 100 items from the list view.

"<View><Query><Where><Geq><FieldRef Name='EmployeeNumber'/>
      <Value Type='Number'>10</Value></Geq></Where></Query><RowLimit>100</RowLimit></View>";

When we started looking at this issue, it was little confusing as they were filtering and pulling only 100 items from a list which has 7000 items. This leads us to look at other throttling contributors, we looked at the lookup columns in the custom list and there were only 2 of them. There was nothing logged in the ULS except the below throttling error message with call stack.

“The attempted operation is prohibited because it exceeds the list view threshold enforce by the administrator. 0x80070024”

Finally we were able to figure out that the “Employee Number” column was not added as an indexed column in the SharePoint List. It is mandatory to add an indexed column(s) for the column(s) that we are using for filtering from SharePoint list views. It is clearly documented in in the UI settings of any list views.

Here the take away is we must need to create an indexed column(s) for the filtering column(s) even from any custom code. Note: maximum indices on a list is 20, so plan your list schema in advance.