Remove the list view threshold (5000 by default)
This limit has always been a bit laughable, and is even more so as we develop more client side applications. In SharePoint 2007 we didn't have this limit and were allowed to make our own mistakes. Now that hardware is so much more powerful, we need this limit removed so that we can build enterprise-class applications.
We are continuing to make our large list experiences better, please keep the feedback coming.
Spring 2018 update:
- We now support being able to manually add indexes to lists of any size (increased from lists up to 20,000 items previously).
- Starting with the February release of the Office 365 Excel client, you will be able to export your full list instead of getting cut off part of the way through.
What we are working on now:
- Predictive indexing will start to work for lists larger than 20,000 items so your views will automatically cause the right indexes to be added to your lists.
In our backlog:
- Being able to index/sort/filter by lookup column types (like person, lookup or managed metadata columns) without being throttled.
- Making sure that our REST APIs support querying in ways that will guarantee that the call will not be throttled.
For a general update on large list capabiltiies, the video on myignite.microsoft.com/videos/53861 (focusing on large lists at 42 minutes and 25 seconds) describes some of the changes that we delivered back in the second half of 2017:
- Modern UI now has a lot of support for adding indexes to large lists and libraries on the fly, reducing the number of throttling errors experienced by our users, and some new UI for browsing through items in large lists with our paging model
- SharePoint runs predictive indexing jobs to automatically add indexes as lists get larger based on your view definitions, and updates these indexes when you add/update your views
Looking forward to receiving more of your feedback.
Andrew Armanini commented
This is a show stopper for even a small office (around 50 users). If you read the "how to deal with large lists" article, it's a catch-22. The only way to get large data in is via creating a list by uploading an Excel spreadsheet. That method only allows the creation of a new list with custom columns. You can't retroactively add indicies and searches to custom columns. So you have an unusable list. And this is without thinking about a document library which excel can't deal with.
This requires the use of third party tools with subscriptions in the thousands of dollars a month, because there's no inbuilt way to migrate list items.
SharePoint Online as it stands is fundamentally broken. You can have 30 million items, as long as there's no more than 5000. SharePoint can do amazing things, except if you actually try to use it.
We at the very least need to be able to retroactively add indexes and switch a custom column to an inbuilt indexable column. It's too easy for a client to work their way into the threshold 5000 trap - with no way out of it.
Martin Coupal commented
Why not enhance and leverage the search engin? You should provide a way to create list/library views based on search and scoped to the list where the results would be presented in a similar layout than a list view (ECB menu etc.).There should be a way to provide real-time search results by using a kind of event driven indexing. This would probably be easier than change how sharepoint stores list data in SQL.
Clients who have worked on prem in a certain way multiple sorts and filters no matter how many indexed columns you have this will not work in o365 with the limits in place , this in turn results in clients looking at other solutions
It's 2017. 5,000 item threshold is just insane. Right now, I can't even setup a workflow to do a lookup against a list with 5,000+ items.
Also, if this is a limit in SQL, maybe MS needs to think about using Oracle for storing their back-end Database.
As a consultant, I absolutely cannot recommend O365 as an Enterprise Application for Document Management due to the 5000 item limit. It is ridiculous. Try going to the Business and telling them that they have to come up with Metadata (which they do not have) just to make the tool work.
SharePoint stores it's content in a SQL database. The 5000 items limit in a document library makes no sense especially when there is an item limit setting in the view, which btw is ok if it's lower than 5000. Many of my customers have up to 20000 documents in a single document library and wants to find duplicates which requires the use of "Show all items without folders" in views. A view without folders but with a item limit of lets say 1000 would work just fine. But it doesn't.
No, it is not reasonable. It is 2017.
David Caranfa commented
I agree with many of the comments that the limit just needs to be removed or dramatically increased. if this means fundamentally changing how SharePoint handles information and interfaces with the database, I still think it needs to be a priority. The reality is enterprises have a ton of data and in many cases don't have the time and resources to spend on planning how to avoid a limitation that is not present in other comparable systems.
Balu Dharmarajan commented
From your answer, it looks to me that you don't really know the limitations of the 5000 item limit or you don't know meaning of word enterprise.
Lets say we have a ticketing application and there is over 10000 tickets in status closed.
How do we get last 10 closed tickets.
Can't because filter don't work even with indexes in place. Throttle !!!. (retrieve all data in list even if over 1 million and filter your 10 items)
Can we export to excel, Throttle !!!.
Add new column and index it for filtering it. Throttle !!!.
So please don't tell filters and indexes work, they don't for lots of production scenario in enterprise.
This is just start, if you have more than 5000 items forget about adding new columns that has to be filtered, there is no way.
we are in process of switching to adobe cms due to this limitations and you are still thinking about it.Take as much time as you want, then probably you don't have to do it.
this limit really needs to raised, 5,000 items is way to low. Needs to be somewhere in the 40-50,000 item limit range for sharepoint online. Please raise the view limit!!!
"The 5,000 item threshold is likely here to stay"
This arbitrary number is the worst limitation you could have thought up to cripple online sharepoint.
Every single other cloud storage has no such restriction - even worse, on premises sharepoint can be configured too in that way - making transition of existing structures de facto impossible.
Personal onedrive folders (private and office365 business) are not limited to 5000 files either.
Counting versions of documents against the 5000 limit just is the cherry on top of this whole craziness. Not even small companies can work with this without completely rebuilding their structures, which is definitely NOT a goal when moving to office365.
This issue has repeatedly blocked transitioning from google docs and is causing all kinds of problems with people ending up on dropbox, box.com with business data because office365 azure sharepoint is just not fit for use.
I do not care wheter it has a gazillion super nifty features to work with (that roughly 0.0005% of all customers use or care about) it has to be able to compete with other cloud based solutions when it comes to the basics.
number of files in a given library - you do not get more basic than that.
Simply syncing a library with a local machine fails if there are more than 5000 items in the library. You can't expect admins to create a new library, migrate part of the old library and configure new sync jobs, then reconfigure the network shares, every time a library hits 5000 items. If the list view limit can't be addressed the OneDrive for Business sync client needs to be more intelligent in splitting up the queries and running the job in chunks.
This 5000 threshold limit is a joke in enterprise uses. Users need to see all the items in a single view to analyze data and make good decision. Setting up multiple views and then combining them in excel or in other apps is cumbersome and bad bad experience. Goal should be to help getting things done easier ways but not complicated ways. Because of this many users moved on to other solutions.
Microsoft only limiting themselves because of this...
This could be resolved if lists wern't so horribly inefficient in SQL. How many SQL queries you want to do just to get a list of items!
The 5.000 item limit for a list is really a show stopper, if you want to use this list as a reference for other lists/libraries (through a lookup, see also https://sharepoint.uservoice.com/forums/330321-sharepoint-search/suggestions/13002084-lookup-on-lists-over-5-000)
In this time it is not acceptable to limit enterprises to this small amount of data.
Guillaume OBSER commented
I plan to abandon Sharepoint in 2017, SharePoint is became to complcated to maintain due to this limit. Using sharepoint was a mistake and this application is clearly the stepchild of the Office 365 tools.
Eric Alexander commented
No matter how much you preplan for large lists, something always changes down the line and you become powerless to do anything about it. Users can keep adding, and adding, and adding, and you as an administrator have NO means to help them. Moving or deleting files to get under 5000 items causes a whole range of problems; it's clumsy, slow, difficult, time consuming, and can lead to 404s as people try to access content that has been moved. Increase the limit, give us a happy hour, or update TechNet to show the actual boundary of SharePoint Online to be 5,000 items, not 30 million.
This is a big show stopper every time we want to convert a team from using Excel, FileMaker Pro or Access to a SP site with multiple lists and look ups.
Check the convoluted behavior of the LVT https://ktnnsharepoint.wordpress.com/2016/08/02/big-lists-threshold/
That’s why it has to be improved.