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.
The 5,000 item threshold is likely here to stay. We are aware that it takes awareness on both sides to get past this limitation: list owners have to build the right fields, indices, and indexed views so that end user queries can run successfully in large lists. And then, end users have to run the correct queries – they can’t just open the root of the list, they have to use a filtered, indexed view.
We want to pursue work that helps on both these ends – for example, proactively indexing more lists, making suggestions to list owners for views to create that will help end users, and helping end users at query time pick filters that will unblock their queries. Additionally, we want to increase our support for running more types of queries in large lists when an index is present – for example, instead of requiring an indexed filter for every query, maybe an indexed could suffice.
No guarantees or timelines yet, but that’s the way we’re thinking about the problem. Does that sound reasonable?
The list view threshold is to maintain performance so that SQL tables aren't locking upon many concurrent requests. Why not some guidance when increasing the list view threshold is acceptable depending on the amount of SQL row locking occurring based on list view queries? I know of some SP deployments where the list view threshold was increased to 20,000 for 2000 users and probably 70 concurrent user sessions during business hours and performance seemed fine.
Andy Tous commented
No, that does not sound reasonable. Your answer might sound logic for an average joe lemon stand, but not for a Fortune 300 company with tons of information on SharePoint. Here's why:
Microsoft support states that SharePoint lists can hold 30 million records. 30 MILLION RECORDS!
Not being able to display more than 5000 items at a time in a view is understandable and logical in order to maintain performance. What is not reasonable at all, thus becoming an impossible task to manage, is the facs that, once you pass the 5000 records limit, you're unable to create filtered list views that return fewer than a hundred results (will not even returns just a few records). THis happens even if the fields being called in the view are indexed.
I tried to create a view in a library of 6000 items that would return 100 records. Nope. Can't do that.
But wait! If I use All Items views, that works! How is that logical?
How in the world would you expect a list that should hold 30 million records to be navigable or manageable without the ability to create views that filter, sort and group?
Your sales and support are both misleading and should state that the limit for SharePoint lists is just 5000 records.
What a joke!
Absolutely DISGUSTED with Microsoft over this issue. First of all, if you can't handle more than 5,000 files, why would you ever sell this product to a business? And second, it is completely unreasonable to expect a small or medium business to have the resources or expertise to figure out how to "get past this limitation". The whole point of your product is to make things easier, but we now have hundreds of hours invested into figuring out why 2 out of 8 users cannot sync. Just 2. Not all 8. If this is truly a legitimate issue, how the other 6 work fine is a mystery to me. Our business has been unable to function for months. We use other products that cannot communicate with SharePoint directly; so syncing is an essential feature and one of reasons we selected OneDrive in the first place. If you aren't going to fix the problem, you need to prominently disclose it as a prohibitive limitation so that you don't trick small businesses into investing in a product that doesn't meet their needs.
The problem is that this LIMIT affects other simple display -- for instance, the method to setup the default OneNote Notebook and Section to use for emails sent to onenote (firstname.lastname@example.org) (https://www.onenote.com/EmailSettings) is restricted by this list view threshold limit (as documented here: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_onenote-mso_mac/notebook-retrieval-error-in-onenote-online/fd41bc98-8537-4df9-8e1e-c15c71bec206).
This doesn't make sense, since OneNote Notebooks are their own thing and there is no need to list the rest of the files in my OneDrive.
Changing how we use OneDrive fundamentally reduces its utility. I cannot reduce my number of files as many are created as part of automated analysis scripts, etc.
Here's the issue. SharePoint lists are supposed to hold 30 million files. 30 MILLION. I understand not being able to display more than 5000 items at a time. What I don't understand is that once you're over 5000 files, you're unable to create filtered list views that return way fewer than 5000 results. I tried to create a view in a library of 13000 items that would return 16 files. Nope. Can't do that. How in the world would you expect a "small" library of 1 million files to be navigable without the ability to create views to filter, sort, group, etc. You may as well say that the limit for SharePoint libraries is 5000 files.
Not reasonable at all!
Noah Stahl commented
I don't think the proposal is reasonable. This is as others have pointed out a fundamental blocker that can't realistically be overcome by anticipating a limit of records in a given view. I think this should be top priority to solve. Surely the technical capability and infrastructure is there to make this possible.
San Mattel commented
5000 default limit is really frustrating. Many cases client not ready to take a risk by increasing this limit and looking at other solutions
If The 5,000 item threshold is likely here to stay, then we need to have better tools to manage it. I dont know I have an issue until a user calls and reports an error. At that time it;'s often too late to provide a reasonable solution.
It would be nice if an admin could set the listview threshold to a lower limit (say maybe 4000. Then, when a user calls to report an issue the admin can bump it up to 5000 to get the users up and running, then add indexes, create views, whatever to resolve the issue, and then set it back to the lower limit. This would give us advancred notice and the ability to test if our workarounds actually work
Jim K commented
Microsoft seems to be positioning sharepoint online as a google drive, box, dropbox competitor - i.e. a file server replacement. None of these other services have this ridiculous restriction. It's a deal killer for using sharepoint online as a file server replacement. Which, in turn, makes us want to look at other solutions such as g suite.
The response by the SharePoint Experiences Team seems to reflect an upside down view of the world. Technology is subservient to people, not the opposite. Asking list owners and users to work around, or against, limits not justified by current technology is not reasonable, and neither is expecting users to think like developers.
Balu Dharmarajan commented
This limit is quite low, I already checked the dll codes and sql queries and it looks like not too complicated to fix, please let me know I will tell you how it can be fixed.
At the Virtual SharePoint Summit, on a slide titled 'Transform business process roadmap', MS announced 'By year end: intelligent indexing for large lists'. Please MS don't forget about any column type: https://ktnnsharepoint.wordpress.com/2016/10/22/column-types-list-view-threshold/
Pramod Nathan commented
Remove List view threshold limit of 5000 by taking advantage of paging
The current list view threshold limit of 5000 with it's workarounds are very frustrating. We can all appreciate that we do not want to fetch too many items from the table (the underlying cause of the restriction). BUT paging exists and Microsoft needs to take better advantage of this:
1. The SharePoint interface is page based. Instead of breaking (which is what the solution does with a warning), MS can change the underlying call so it breaks down the call to SQL to fetch data as needed per page/set of pages. When the user navigates to the next page/set of pages - call SQL for the next set of data (e.g. if I am on the current view with 5000+items, I really don't care about about item 5000+1 immediately. BUT I do care that I can manipulate the entire set of items and that I can get to item 5001). So just fetch the first few page worth of items.
2. For sorting and filtering, the call can go to SQL on click to fetch the top X number of items depending on the filter or sort. (e.g. if the page has 5000+items and we want to sort by a column, it can call a SQL statement that does this but sends back only the top 3-4 pages or how many ever are feasible)
3. It will also make the underlying APIs very robust in terms of being able to create multiple calls of data to generate a large data sets that are stored in SharePoint lists and libraries.
the 5,000 is pushing us to pursue other solutions outside SharePoint.
We created a list to hold user's stats; and for stats purposes it is worthless to retrieve chunks of data when you want to know how many selected a, b, or c, or group them by hour.
As a workaround, we connected the list to PowerBI. From there we created a dashboard which actually can work with all the data at the same time. Until now, this workaround worked just fine, but we just got a message saying that dashboard sharing will no longer be part of PowerBI free.
We just want SharePoint to work and not to start thinking on workarounds or 3rd party applications to make it effective and efficient.
Steven Derveaux commented
And another customer bites to dust...
Keep in mind that the search box in document libraries (in the modern experience) is also struggling with this limit. Working with folders can overrule the 5000 items, but the new-autocompleted-search cannot cross this limit. MS Support pointed us to use the Search Center or create our own webparts. Meanless to say: you cannot replace this autocompletion search webpart because you cannot customize the modern view! Aargh!
So what can you do? switch back to Classic Design but then you loose the new copy functionality!
I have no words for this...
This limit of 5000 thousands is preventing us of creating many solutions in Sharepoint.
SharePoint is heading backwards in so many ways. As another contributor said, it's driving business away from SPO - but then, maybe that's what Microsoft want. SharePoint will become a simple (and small) file store). Lots of publicity re huge amounts of storage space, millions of items in lists etc. - except it's effectively unusable.
What makes this limit really unusable is that you cannot create / amend indexed fields. How useless is that!
Or am I being cynical?
The 5000 item threshold is ridiculous when Microsoft's own guidelines say a library can hold 30 million items. How in the world is anyone supposed to come up with indexes and filters to split that into 5000 item chunks and have it be anything but worthless? It's already difficult enough when people have barely more than 5000 and a view using Groupings, and suddenly it stops working after a migration.
Juan Pineiro commented
This has been a major issue for us and driving business off of SPO. It's not just about awareness, indexes or filters. I understand nobody need 5k+ items sent to a page, but after 5k, many setting features no longer work. We have a list with 25k items and you can't add new indexes or change calculated columns on it. Is that a bug or a protective feature?