List View Threshold: What Still Works at 10,000 Items

I’ve lost count of how many times I’ve hit this. A list quietly grows past 200,000 items, somebody asks for a filtered view of it, and the code that has worked for two years answers with this instead:

Microsoft.SharePoint.Client.ServerException: The attempted operation is
prohibited because it exceeds the list view threshold.

The code didn’t change. It never does.

CSOM has an escape hatch for this, AllowIncrementalResults, and it gets a post of its own. What I could never answer was the obvious follow-up. Is CSOM the only place with an answer? Every time I’d needed to filter past the threshold I’d ended up back in CSOM, but I’d never actually gone and checked what REST and Graph do. Saying “I don’t know” for two years is a long time.

So I took the harness from the CAML joins benchmark, seeded a second four-list chain at 10,000 items per list, and ran everything again. Ten thousand is not two hundred thousand, but it’s twice the threshold, which is where the behaviour changes.

What actually breaks at the SharePoint list view threshold?

Not reading. I can still pull all 10,000 rows out of any of these APIs, and it costs two requests. What breaks is filtering: the moment a <Where> or a $filter touches a column SharePoint hasn’t indexed, the query is refused, and it’s refused even when the answer would be ten rows. The threshold is about the scan, not the result set. That single sentence explains almost everything below.

Reading Was Never the Problem

The first thing I measured is the thing nobody worries about, and it turns out to be fine:

Two requests each, because 10,000 rows is two pages of 5,000. An unfiltered read past the threshold is allowed on all three. If your job is “give me the whole list”, the threshold barely exists.

That’s worth saying plainly because the error message sends people hunting for the wrong fix. You don’t need to restructure your list to read it.

The Flag That Shouldn’t Work, and Does

Here’s the one I got wrong, and I was confident about it.

The most useful thing a CAML join does is let you filter on a column that lives several lists away, by projecting it and putting the <Where> on the projection. But a projected field is not a column on the list. It doesn’t exist there, so it cannot be indexed. And the documented rule for AllowIncrementalResults is that the fields you filter and sort on still have to be indexed.

I was sure that made the technique impossible past 5,000 items. I wrote the probe expecting to have to publish a correction.

var query = new CamlQuery
{
    ViewXml = viewXml,               // <Joins>, <ProjectedFields>, <Where> on customerNo
    AllowIncrementalResults = true,
};

var all = new List<ListItem>();
do
{
    var items = list.GetItems(query);
    context.Load(items);
    context.Load(items, i => i.ListItemCollectionPosition);
    context.ExecuteQuery();

    all.AddRange(items);
    query.ListItemCollectionPosition = items.ListItemCollectionPosition;
}
while (query.ListItemCollectionPosition is not null);

What’s happening here?

1. Without AllowIncrementalResults, this exact query is refused: SPQueryThrottledException, straight away, no rows.
2. With it, the same query returns all 5,000 matching rows in two requests. The filter is on customerNo, which is projected from a list two hops away and cannot be indexed anywhere.
3. ListItemCollectionPosition is the cursor and it is not optional. A <RowLimit> on its own doesn’t page, it truncates, and truncation is the failure mode you don’t notice.
4. You need both properties loaded. context.Load(items) alone won’t populate the position on every code path, and a null cursor looks exactly like a finished result set.

So the join survives the threshold. My best guess at why is that the projection is resolved after the join rather than as a scan predicate, but that’s inference, not something I can prove from the outside. What I can say is that it returned the right 5,000 rows, ten times in a row.

Then I checked whether the depth matters, because it easily could have. Everything above filters on a value two lists away, which is the shape I’ve written about before. So I moved the query one list further back and made the projection travel three joins instead of two:

Identical, right down to the request count. Whatever the flag is doing, it isn’t running out of road at the second hop, and a filter three lists from the one you’re querying is as viable past the threshold as a filter one list away.

Three APIs, Three Different Refusals

Filter on a column that genuinely isn’t indexed, and all three refuse. How they refuse is where they differ.

Graph wins this one, and it isn’t close:

Field 'Title' cannot be referenced in filter or orderby as it is not
indexed. Provide the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly'
header to allow this, but be warned that it may fail randomly.

Add the header and the same request returns 200. Read the header’s name again before you reach for it, though. Somebody at Microsoft went to the trouble of putting “MayFailRandomly” in an API contract, and that is not the kind of thing you build a nightly job on.

REST answering 500 for this is the one that annoys me. Filtering an unindexed column on a large list is a completely predictable, documented, business-as-usual refusal. It is not an internal server error.

The REST Trick That Only Works Under 5,000

In the previous post I found something I didn’t expect: SharePoint REST will let you filter on an expanded lookup’s column, even one that isn’t the lookup’s ShowField.

$filter=CustomerLookUp/customerNo eq 'C-NARROW'&$expand=CustomerLookUp

At 1,000 items that returns 200 and it’s the single thing that got the OData route down to two requests. At 10,000 items it returns 500, in every scenario I threw at it, along with the batched version of the same call.

So it’s a sub-threshold trick. It works beautifully right up until the list is big enough for it to matter, which is a fair description of a lot of SharePoint behaviour. If you have built anything on that shape, it has an expiry date measured in rows.

The One That Lies to You

RenderListDataAsStream is how you run a CAML join over REST, and in the last post it came through with the projected columns intact. Past the threshold it does something worse than failing.

Asked for 100 matching rows out of 10,000, it returned 47. Asked for 5,000, it returned 2,504. One request, 200 OK, no error, no NextHref to follow, no indication that anything is missing. It stops when the scan window closes and hands you what it found so far as though that were the answer.

A truncated result with a 200 on it is the worst outcome in this whole post. Everything else either works or tells you it didn’t.

What It Costs in Memory

This is the part I hadn’t measured before, and it’s the strongest argument in the post. Below, allocated managed bytes while answering the same question: 100 matching rows out of 10,000.

One hundred rows. Two hundred megabytes.

The split is exactly the Filter column. Every approach that filters on the server materialises about a hundred rows. Every approach that filters in C# has to pull the whole list into memory first, then throw away 99% of it, and at 10,000 items that is 200 MB of allocation to produce 100 objects you keep. Your production lists are twenty times bigger than my test list.

Two things surprised me here. Allocation does not rank the same as bytes on the wire: CSOM allocates roughly eleven times its payload because a ListItem is a heavy object with a field dictionary attached, so 17 MB of response becomes 200 MB of garbage. And the join is not the memory winner when you ask for everything, only when you ask for some. Unfiltered, lean OData that fetches nothing but lookup ids allocates 17 MB against the join’s 105 MB, because the join is honestly returning full rows and OData is returning integers.

The Whole Field at 10,000 Items

Filtering to 5,000 rows of 10,000, entering at the order tasks list, two hops to the customer:

The join is still first, and at 10,000 items it is now first by a wider margin than at 1,000, because everything else has to page and it only has to page twice.

Note what happened to Graph’s reverse walk: 62 requests. The or-chains that fitted in a handful of calls at 1,000 items become sixty-two at 10,000, and batching turns 62 round trips into 5 while leaving all 62 queries exactly where they were.

Gotchas

• The exception message is localised. My tenant answers in Danish: Den forsøgte handling er ikke tilladt, fordi den overskrider grænsen for listevisning. My first probe matched on the string “list view threshold” and therefore never detected a single hit. Match on ex.ServerErrorTypeName == "Microsoft.SharePoint.SPQueryThrottledException" instead. Any retry logic or alerting built on the message text is broken in every tenant that isn’t English.
• <RowLimit> without a cursor is a truncation, not a page. This is how you get a wrong answer with no error. One of my own arms returned 47 rows out of 100 because it read the first 5,000 items of a 10,000 item list and then filtered what it happened to have.
• Batching collapses queries, never pages. A cursor only exists once the previous response has arrived, so a batch can carry every list’s page one and then has to go again for page two. That’s true of CSOM’s ExecuteQuery, of SharePoint’s $batch, and of Graph’s. Graph’s docs suggest batching as a way around URL length limits; I measured the same 91 clause ceiling inside a batch as outside it, so that particular workaround doesn’t apply here.
• <In> past 60 values is fine, and I’m one of the people who told you otherwise. I repeated the 60-value claim in the joins post and cited the archived 2013 article it comes from. On 10,000 items with an indexed lookup column I ran 50, 60, 61, 100 and 500 values, and every one of them returned. The 500-value hard cap is real: 501 still throws Value does not fall within the expected range. It’s the 60 that I can’t reproduce.
• The ceiling moves. One reverse-traversal arm hit the threshold in one scenario and sailed through the same shape of query in another. Large list throttling is a service-side feature with a time-of-day component, so “it worked this morning” is not a test result.
• Your own tooling will hit this too. The setup step of my harness crashed while verifying the data it had just seeded, because it ran an <IsNull> check with <RowLimit>10</RowLimit>. Ten rows requested, still refused. I had written that check myself and still didn’t see it coming.

Wrapping Up

Reading a big list is easy on every API. Filtering it is where they separate, and they separate hard: CSOM throws something you can catch, REST throws a 500, Graph throws a 400 with instructions, and RenderListDataAsStream hands you a partial answer with a 200 on it.

The advice from the previous post survives the threshold, with one line added. Make SharePoint do the join, set AllowIncrementalResults = true, and page with the cursor. It stays the fastest correct answer at 10,000 items, and it’s the only approach that filters server-side without resolving the ids yourself first, which past the threshold has stopped being a performance question and become a memory one.

Everything else is holding your whole list in RAM to find a hundred rows in it, and it is doing that whether or not anyone measured it.

Jeppe Spanggaard

Software developer & co-founder of Pinksky, working in C#/.NET, Azure, Microsoft Graph and SharePoint. I write these posts in Danish and use AI to help with the English - the code, the numbers, and the mistakes are all mine. More about me →