Reading a SharePoint list with more than 5,000 items without hitting the list view threshold

A list grows past 5,000 items and a script that has run for years starts failing. Microsoft documents the message a browser user sees in SharePoint Online as "The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited." (troubleshooting article). The confusing part is that the failing query often asks for very little: a filter that would match twenty rows fails exactly like one that asks for everything.

The threshold counts the rows a single database operation has to process, not the items a list holds and not the rows a query returns, so the remedy is always the same: make every request touch fewer rows, with an indexed first filter and with paging. The documentation cited here was read on 12 November 2026. The two scripts use only parameters and endpoints confirmed in that documentation; we did not run them against a tenant for this article.

What the threshold is, and what it is not

It is not a capacity limit. The SharePoint limits page states that "A list can have up to 30 million items and a library can have up to 30 million files and folders." The boundaries page for SharePoint Server, which is marked as applying to 2016, 2019 and Subscription Edition, gives 30,000,000 items per list and 30,000,000 documents per library as supported limits.

The threshold is defined on Microsoft Support as "the maximum number of list or library items that a database operation, such as a query, can process at one time. Operations that exceed this limit are blocked." The same article explains the origin: when an operation "causes more than 5,000 rows to be locked at once, then it's more efficient for SQL Server to temporarily lock the entire table", which blocks every other user of that table.

SharePoint Online

The value is fixed. Microsoft Support says "SharePoint Online version doesn't support changing the List View Threshold", and gives the reason as protecting "users on other tenants from performance issues when large data sets are retrieved" (server-only guidance). There is no daily window and no administrator override. Indexes, filters, folders and paging are the only tools.

SharePoint Server Subscription Edition

On-premises the threshold is a setting of each web application. The path Microsoft Support gives is Central Administration, Application Management > Manage Web Applications, select the web application, then General Settings > Resource Throttling. The settings documented on that page are:

• List View Threshold: 5,000 by default. Microsoft's advice is direct: "We don't recommend changing it to avoid performance degradation."
• List View Threshold for Auditors and Administrators: 20,000 by default, applied to queries that programmatically request the override.
• Object Model Override: on by default; it is what allows such a request.
• Daily Time Window for Large Queries: a period, set "in 15-minute increments and up to 24 hours", in which the limits are ignored. An operation started inside the window "continues until completion (or failure) even if it doesn't finish within the specified time window."

The Learn boundaries page adds three defaults that differ from 5,000: adding or removing a column index is allowed up to 20,000 items, and deleting a list or folder, or renaming a folder within the same library, up to 100,000.

One caution: the Microsoft Support articles name SharePoint Server 2016 and 2019 and call the steps "standard from SharePoint 2016"; only the Learn boundaries page names Subscription Edition explicitly.

Indexed columns

An index is what lets a filter avoid reading the whole list. The documented rules, from Add an index to a list or library column and Use filtering to modify a SharePoint view:

• "You can add indexes on up to 20 columns on a list or library."
• Only the first indexed column of a filter counts: "SharePoint selects the first indexed column in a query, and then uses the rest of the columns to select your data." That first column "should return fewer items than the List View Threshold."
• Conditions must be joined with AND. With OR, the database "must scan the complete list".
• "using an indexed lookup column to prevent exceeding the List View Threshold doesn't work."
• Items in the recycle bin "count against the List View Threshold".

In SharePoint Online, indexes are also created automatically, "when saved views have columns used for sorting or filtering" and "when sorting in the modern experience". The second case has a documented ceiling: it "is limited to lists and libraries with less than 20,000 items. If it is larger than 20,000 items, then the index will be created in the background" (guidance for all versions).

Why a filter on a non-indexed column fails

Without an index the database has to read every row to test the condition, and that read is what crosses the threshold. Microsoft's own example: "You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled." Adding an indexed condition first, "Class = Mammals AND Species = Dog", makes the same question answerable. The number of dogs never mattered.

Paging with PnP PowerShell

Get-PnPListItem documents -PageSize as "The number of items to retrieve per page request" and -ScriptBlock as "The script block to run after every page request". Both belong to the All Items and By Query parameter sets. With -Query, columns are chosen with a <ViewFields> clause, because -Fields is not available.

# Requires the PnP.PowerShell module and an Entra ID app registration.
$siteUrl  = "https://contoso.sharepoint.com/sites/operations"
$clientId = "00000000-0000-0000-0000-000000000000"
$listName = "Orders"

Connect-PnPOnline -Url $siteUrl -Interactive -ClientId $clientId

# 1. Every item, 2,000 per request, only the columns that are needed.
Get-PnPListItem -List $listName -PageSize 2000 -Fields "Title", "Region" `
    -ScriptBlock { Write-Host "Page retrieved" } |
    ForEach-Object {
        [pscustomobject]@{
            Id     = $_.Id
            Title  = $_.FieldValues.Title
            Region = $_.FieldValues.Region
        }
    } |
    Export-Csv -Path ".\orders-all.csv" -NoTypeInformation

# 2. A subset. Region must be an indexed column and must match
#    fewer items than the threshold.
$caml = "<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='Region'/></ViewFields>" +
        "<Query><Where><Eq><FieldRef Name='Region'/><Value Type='Text'>EMEA</Value></Eq></Where></Query></View>"

Get-PnPListItem -List $listName -Query $caml -PageSize 2000 |
    ForEach-Object { $_.FieldValues.Title }

Keep the column list narrow: the same page notes that SharePoint refuses a query naming more than 12 lookup columns, and Person or Group, Managed Metadata, Created By and Modified By each count as one.

Paging with Microsoft Graph

The list items reference supports $filter and $expand, with Sites.Read.All as the least privileged permission, and states two constraints: "Filtering works best on indexed columns" and "When filtering on indexed fields, the service can only filter one indexed field at a time." Results arrive "all at once or in multiple pages". The paging guide gives the contract: keep calling "until the @odata.nextLink property is no longer returned", use "the entire URL", and "Don't try to extract the $skiptoken or $skip value and use it in a different request."

# Requires the Microsoft.Graph.Authentication module.
Connect-MgGraph -Scopes "Sites.Read.All"

$siteId = "contoso.sharepoint.com,00000000-0000-0000-0000-000000000000,11111111-2222-3333-4444-555555555555"
$listId = "aaaaaaaa-bbbb-4ccc-8ddd-eeeeeeeeeeee"

# Quantity must be an indexed column.
$uri = "https://graph.microsoft.com/v1.0/sites/$siteId/lists/$listId/items" +
       '?$expand=fields($select=Title,Quantity)&$filter=fields/Quantity lt 600'

$rows = while ($uri) {
    $page = Invoke-MgGraphRequest -Method GET -Uri $uri
    foreach ($item in $page.value) {
        [pscustomobject]@{
            Id       = $item.id
            Title    = $item.fields.Title
            Quantity = $item.fields.Quantity
        }
    }
    $uri = $page.'@odata.nextLink'   # absent on the last page, which ends the loop
}

$rows | Export-Csv -Path ".\orders-graph.csv" -NoTypeInformation

We found the header Prefer: HonorNonIndexedQueriesWarningMayFailRandomly documented in one place, the SharePoint Embedded guide on searching containers and files: "When a container has more than 5,000 items and you enumerate with $orderby, include the Prefer: HonorNonIndexedQueriesWarningMayFailRandomly header required for large ordered enumerations." The list items reference does not list it among its request headers. Its name is its own warning: it is not a substitute for an index. Invoke-MgGraphRequest accepts it through -Headers.

Paging with SharePoint REST and CAML

For _api/web/lists/.../items, the list items guide says the call "will return the first 100 items" by default and that "The OData $skip query parameter doesn't work when querying list items. In many situations, you can use the $skiptoken option instead." The OData operations guide documents $top for page size and gives the token format:

GET https://contoso.sharepoint.com/sites/operations/_api/web/lists/GetByTitle('Orders')/items?$top=2000
GET https://contoso.sharepoint.com/sites/operations/_api/web/lists/GetByTitle('Orders')/items?$skiptoken=Paged=TRUE%26p_ID=5

$skiptoken lets you "skip over items until the specified item is reached and return the rest". The page warns that paging is ordinal: if items are deleted between two requests, the next page can skip rows.

In CSOM, a CAML query pages with a row limit and a position: CamlQuery.CreateAllItemsQuery(int rowLimit, params string[] viewFields), the read-only ListItemCollection.ListItemCollectionPosition returned with a page, and the settable CamlQuery.ListItemCollectionPosition that carries it into the next request. The .NET reference lists these signatures without describing their behaviour.

Folders and metadata navigation

Folders help because of how they are stored: "When you create a folder, behind the scenes, you're creating an internal index. This internal index is also created for the root folder". A subfolder counts as one item in its parent, not as its contents. But "A folder can contain more items than the List View Threshold", and then an indexed filter is needed again. Get-PnPListItem -FolderServerRelativeUrl scopes a read to one folder.

Metadata Navigation and Filtering "can automatically select the best index to work every time a view is loaded". When no index fits, it runs a fallback query that shows "Up to 1,250 of the newest items": a partial answer, which a script should not depend on.

What still fails, and when to restructure

Paging and indexes do not unblock everything. Microsoft Support lists operations that stay blocked on a list over the threshold because they need the whole list: "adding or deleting an index, creating a sort in the view definition; displaying a column total; and adding, updating, or deleting calculated fields." Further documented limits:

• Site template creation is blocked on sites with a list over the threshold.
• Turning on Cascade Delete or Restrict Delete for a lookup column is blocked.
• In SharePoint Online, above 100,000 items "you can't break permissions inheritance on the list, library, or folder", nor re-inherit it.

On SharePoint Server these are the operations to schedule in the daily time window. SharePoint Online has no window; the index article says "In SharePoint, you can manually add an index to a list of any size", so an index remains the first thing to try.

Restructuring is the answer when no indexed column can serve as a first filter under 5,000 matches, or when the queries need OR logic or more than 20 indexes. The documented option is to "distribute all the items in a list or library into multiple folders such that no folder has more than 5,000 items". For finding rather than enumerating, list search is an alternative: "SharePoint Search has its own indexing mechanisms, and it's not subject to the List View Threshold".

Related reading

• The last Graph page is not proof of tenant coverage: what following @odata.nextLink to the end does and does not establish.
• How to enumerate every site in a SharePoint tenant: the same paging discipline one level up.

Tags#sharepoint#graph#powershell#development#havecode