A SharePoint list can hold 30 million items and still feel broken at 3,000, or sit at 3,000 rows for years without a single problem. The 5,000-item list view threshold everyone quotes is the cheapest limit to fix: add an index and a filtered view sails past it. What stops a list working as a database is lookups, concurrency, delegation and reporting, and none of them throw an error until the damage is done. They show up about eighteen months in.
| Limit | What it means in practice |
|---|---|
| List view threshold | 5,000 items per query without an indexed filter, effectively no ceiling with one |
| Indexed columns per list | 20, with SharePoint adding some automatically as a list grows |
| Lookup-type columns per view | 12, and Person/Group and Managed Metadata columns count toward it too |
| Absolute list size | Around 30 million items, though it slows well before that point |
| Power Apps non-delegable record limit | 500 by default, adjustable up to 2,000 |
| Power Automate Get items, paginated | Up to 100,000, but still needs an indexed column for any filter past 5,000 |
What the list view threshold does
The threshold blocks a query the moment it can't lean on an index, which is a different event from a list passing 5,000 rows. Filter a view by an indexed column, and as long as the filtered result comes back under 5,000 items, the query runs fine no matter how big the underlying list is. SharePoint Online now adds indexes automatically as a list grows, and a maker can add up to 20 manually. What still fails is an unfiltered "all items" view, or sorting and grouping on a column that isn't indexed, since neither can be pushed down to an index the way a filter can.
There's a separate, harder ceiling further out: a SharePoint list technically holds up to around 30 million items before you hit an absolute wall, and even that doesn't throw a clean error so much as get progressively slower to work with. Microsoft has no plans to raise the 5,000-item threshold itself, because it exists to protect a shared, multi-tenant environment. Building around it with indexes is the normal way of working.
Where SharePoint holds up better than its reputation
A job register, an asset list, a quote log, a simple approval queue: these are the things people build on SharePoint constantly, and for good reason. It's included in the Microsoft 365 seat a firm already pays for, gives sortable views, choice fields, people pickers and alerts out of the box, and Power Automate can watch it and fire off a reminder or a Teams message without anyone writing code. For a firm under 50 staff running a few thousand rows through a list, that's a solid, low-maintenance setup that will hold for years.
The built-in conflict handling is better than its reputation too. Two people editing the same item through the standard list form get a proper save conflict warning, not a silent overwrite, and version history makes recovering from a bad edit a few clicks, not a support ticket. Most of the dismissiveness aimed at SharePoint lists online is aimed at the SharePoint of a decade ago rather than the indexed, threshold-aware version most firms run today.
The ceilings that get you
None of the following show up when a list is small. All of them show up once it's doing real work.
Lookups and relationships
A single view can carry at most 12 lookup-type columns, and that cap includes Person/Group and Managed Metadata columns, plus the built-in Created By and Modified By fields. A list with a handful of people-picker columns and a couple of lookups to other lists blows past that limit faster than most people expect. SharePoint does have a real referential integrity feature, enforce relationship behaviour, letting a lookup column cascade or restrict a delete, but it needs an indexed, single-value lookup column and Manage Lists permission to turn on, caps cascade delete at 1,000 items per operation, and can't be enabled at all once the parent list has already passed the view threshold. The one feature meant to protect data integrity switches itself off exactly when a list is large enough to need it.
Concurrency
The built-in save-conflict warning only covers a person typing into the native list form. Writes from a flow, a Power App, or an API call have no equivalent lock, so two automated processes updating the same item close together can silently overwrite each other rather than queue, wait or error. A job register touched by a handful of people a few times a day is fine. Put several automated writers on the same rows and you have a race condition waiting for the wrong afternoon.
Delegation, the one that bites
This is the limit that catches people out, because nothing fails loudly. Power Apps only pushes some functions down to SharePoint to run server-side, that's delegation. Anything it can't push down gets pulled back and processed locally instead, capped at 500 records by default and 2,000 at the outside if a maker raises it. Common non-delegable culprits against SharePoint include the Search() function, several nested Or() conditions across mixed column types, and sorting on certain computed or multi-value columns. The app still runs, the screen still looks fine, it's just quietly working off a slice of the list once that list grows past the cap, with nothing louder than a small warning icon most people learn to ignore.
Power Automate has the same mechanism under a different name. The Get items action defaults to a small batch, but pagination can be enabled to retrieve up to 100,000 items across batches, still depending on an indexed column for any OData filter once the list passes 5,000 items. Filter on a column that isn't indexed and the flow gets refused at the same threshold that blocks a view.
Reporting
Power BI's SharePoint list connectors inherit the same ceilings rather than sidestepping them. A report built against an unfiltered "all items" query slows down or times out as the list grows, and there's no native join across two lists the way a relational data source handles it. It's workable with the newer connector, indexed OData filters, or landing data into a proper reporting table on a schedule instead of live-querying the list every refresh, but each of those is a deliberate engineering decision someone has to make and maintain.
Patterns that buy you another year
Hitting one of these is not a reason to rebuild tomorrow. A few habits keep a list working well past the point where the threshold would otherwise start biting.
- Archive instead of accumulating. Move closed or old rows into a separate archive list on a schedule so the working list stays small and every index stays useful.
- Index the columns you filter or sort by, ahead of time. Waiting for the threshold error to tell you which column needs an index is the expensive way to find out.
- Route writes through one flow rather than several people, apps or scripts writing to the list directly, so there's a single place handling updates instead of processes racing each other.
- Make the default view a filtered, indexed one rather than "All items", so nobody triggers the threshold by accident just opening the list.
- Land data for reporting instead of live-querying it. A scheduled flow that copies rows into a purpose-built reporting table beats pointing Power BI straight at the list on every refresh.
Where Dataverse stops being optional
Watch what starts breaking, not the row count. Once relationship behaviour won't turn on because the list already ran past the view threshold, once a Power Apps screen is serving incomplete results from a non-delegable query no one flagged, or once two systems are racing to write the same item with no lock between them, the engineering time spent working around the platform starts costing more than moving the data would. That's the point where Dataverse pays for itself. SharePoint didn't get worse; the job outgrew what a list was designed to do.
Common questions
Does the SharePoint list view threshold apply to every query? No. The threshold only blocks a query that can't use an index. Filter a view on an indexed column and return fewer than 5,000 matching items and the query runs past the raw item count fine. SharePoint Online adds indexed columns automatically as a list grows, and a maker can add up to 20 manually. What still fails: sorting or grouping on a column that isn't indexed, or an unfiltered "all items" view on a large list.
What is the real limit before a SharePoint list needs Dataverse? The raw item count is rarely the trigger. The more common ones are a Power Apps screen quietly returning incomplete results because a query wasn't delegable and got capped at 500 or 2,000 records, a lookup relationship that won't enforce cascade or restrict delete because the list already passed the view threshold, or two systems writing to the same item with no lock between them. Any of those is a stronger signal than the number of rows.
Can Power Automate read more than 5,000 items from a SharePoint list? Yes, with pagination turned on in the Get items action, which can retrieve up to 100,000 items across batches. That still depends on an indexed column for any filter once the list is over 5,000 items. An OData filter on a non-indexed column gets refused at exactly the same threshold that blocks a view.
First published at https://kove.nz/insights/sharepoint-list-limits
Top comments (0)