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.
I cannot believe that anyone would scroll through 5,000+ list items (& documents), BUT we do need to be able to easily filter a 5,000+ lists and/or retrieve calculations on the results of 5,000+ so is a possible solution.....
Provide with every list a filter/refiner web part that appears above the list if switched on lets say in the view settings. The web part could auto show the fields that have been set in the view settings, so users can select from option fields, select dates, etc...The list on open can either show no data or the first 'x' number of results based on the view and a message informing the user that only 'x' is being shown. If the refine/search results is over 5,000+ show the max amount but provide any count/sum set in the view. Just putting it out there (maybe someone below has !).
Since other online storage services don't have this problem, it's pretty clear it's caused by the way SP gets data from the db. SP is an antiquated server based platform probably not well suited to online use in its current form. The idea that SP has to request all items in a list to return a filtered view of say 25 items is absurd. The filtering should happen before the request gets sent but SP simply doesn't work that way and until it's modernized (or in typical Msft fashion, replaced), it's not fit for enterprise use.
The 5,000 file limit is a massive problem for small business users of Office 365. Those wanting to move to a one-stop modern Office/Email/File management system face unreasonable restrictions due to this limit. While SharePoint is powerful and does make overcoming this limit possible with customized list views, most small businesses aren't going to take the time to administer SharePoint sites. Instead they're stuck paying the monthly subscription fee for Office 365 and paying more for a Dropbox or Box licence.
You have to fix this and make the SharePoint/OneDrive for business services more appealing for those wishing to migrate from Dropbox or Box.
Please make searching for items in document libraries with more than 5000 items work.
If a search query is triggered from within the document library, and the query pulls back a large number of results - clicking the 'See more results' link shows no items at all. A really poor experience and confusing for end users.
Franchesca V Havas commented
I agree that this limit needs to be raised. As a SharePoint developer from the 2001 database beta version we used in Texas to today I can say that this is by far one of the top 10 problems we are encountering today.
Please raise the view limit!
Marc Wenning commented
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!!!
Andy Dale commented
The 5000 limit causes me endless problems. I keep creating and changing views, have all key columns indexed but still get call from irate customer every few months saying 'Threshold Limit has been reached'. The issue is that to pull all items back on an individual (patient) we have a link that searches all the items in the list. Please, please find a way to fix this biggest frustration.
This limits creates a ton of confusion for IT and end users. Our customer thinks their data is missing. 5,000 item limit is simply way too low, especially when SharePoint lists is seen as a replacement for databases in certain cases. You HAVE to come up with something better...
Within our 400 person organization, I'm the only IT resource to keep everything IT running. File shares are easy to maintain - they just work. This limitation on views effectively prevents us moving file shares to SharePoint as I lose the ability to edit permissions when there are more than 5000 items in a library - that's after breaking down 8 main shares into over 50 libraries to try and get around this limit.
I wasted many hours zipping and shuffling data around only to find users had once again gone over 5000 items afte a few weeks. It's too hard to maintain.
Pascal van Alphen commented
Hi SharePoint Team,
I'm sorry but that line of thinking doesn't sound reasonable to me. I agree that there are definitely situations in which you can work around the issue using appropriate views or indexes, but there are simply too many common business scenarios in which this is not possible.
What if the most important business information around a customer's documents - the fields you really want to filter on - are multi-value lookups? In that case you get no index option, no filter option, so in essence your existing most important library views become useless.
Limiting the view based on something like a start and end date is often not a workable solution.
Then there is the issue where you need to query your entire list based on certain criteria. I've run into this problem not only in views but also in some selections done from workflows. As a UI limit I can fully understand a 5000 limit, but for a selection in the background?
Please understand that this is truly a deal-breaker for several of the customers that I work for. If you seriously want to keep talking about SharePoint as providing 'enterprise grade' solutions (quote from the Future of SharePoint event on May 4th, 2016) then you simply have to get rid of this limitation.
Andi Fandrich commented
Microsoft should not "think about it" but just plain fix it. It is 2016! Ever heard of big data?
Sol Birnbaum commented
Hello SharePoint Team, we have lists over 5000 items, but they are distributed across around 100 root sub-folder. This works great as far as responsiveness, HOWEVER the new library look refuses to open the library (it open in old look). While this is still in preview, please can you allow 5000+ item in new view, provided they are being managed correctly? We are an MSP and our clients have similarly size and well structured shares. We won't be able migrate them to SharePoint with this limitation and will need to explore other EFSS options; we would prefer to stay with SharePoint though.
David Shumate commented
I agree with all of the comments here. I believe that while this limitation exists, and the end users I support expect to be able to use the Windows Explorer view (which works sometimes and other times not) to access a document library with greater than 5000 items, that the "Open with Explorer" ribbon menu item should be disabled since the behavior is uncertain. My end users do not care to learn technical details about an issue and are not interested in descriptions of the same technical issue as a response to there questions about why it is not working. To me, if it isn't functioning 100% than it should be disabled when the condition exists for which it may not work, and to that end, maybe it will put a little fire under the issue to get it moving. This has been a problem for a long time based upon my reviews of technical articles going back years, and given the present strategy for Microsoft's cloud vision, would seem to be a self imposed barrier to making that vision a reality. Please address this issue asap!
Marie Castine commented
How about add an easy way to archive old items when the list gets close to 5000.
Aymeric de Montpellier commented
At least the threshold should not be activated based on the first argument of the WHERE clause....
For example: I got a list with the columns `Data`, `Week` and `Year`. I have 1,000 items recorded each week.
I need all data from Week 1 to Week 3 (for reporting purpose). If I do my WHERE clause with « `Year` = 2015 AND Week >= 1 AND Week <= 3 » then I'll get an error, even if the final result will return less than 5,000 items. And I can't do « Week >= 1 AND Week <= 3 AND Year = 2015 » because it will return an error as well. And again if I do « Week <= 3 AND Week >= 1 AND Year = 2015 » because it will look at Week 1, 2 and 3 for all the years recorded.
Moreover, when we find that a list is too big (> 5,000 items), we cannot index the columns because it throws the threshold error.... We can only do it during the special time set up by IT. In my company, the servers are in the USA and I'm in France, so I have to index my columns at 1 am because of the threshold, during the special time!
Also the Lookup columns limitation as well as the Workflow limitation are very boring.
I'm pretty sure you can create something safe for the database, and not painful for users.
In the 90's we put everything in folders and subfolders. It sucked. Now with SharePoint, we can tag files with metadata and create views to organize our files dynamically. If I have to break up everything into 5,000-item libraries, I'm basically going back to folders again -- the dynamism is entirely lost. YES I KNOW I can create special queries or buy web parts to make it look like everything is stuck back together. But what about my users? They don't understand that stuff, they want it to just work. How do I begin to explain a 5k limit to them, and really, why should I have to? It shouldn't be essential to their day to day.
> Other times we simply need more than 5000 items. Period.
Echoing Marc's comments. I just built a Document Center to house my organization's HR files. It works great. But we're already at 25,000 items, and we'll likely be at 100,000 items in a few years. I'm not going to even try to predict how to divide 100,000 items into 5k chunks in a future-proof way. And how would I sell project that to my directors? "Sir, I need probably 50+ libraries, requiring extensive build and training, instead of just the one." "Why?" "Well, you need to know the limitations of Windows Server to understand." I built workarounds for the 5,000-item limit for now, but honestly, if it weren't for SP's retention policies, we wouldn't have considered SP a future-proof storage solution *at all* thanks to this stupid limit.
The answer is NOT more education. The answer is raising the limit to 1 Million Files. That's your goal. And honestly, the question isn't whether you'll do it, it's which competitor will beat you to it.
I think a lot of the problem is education. Maybe Microsoft could produce some training materials or another edX SharePoint course specific to overcoming the threshold limits and explaining them. I attended this one back in August, which was very good: https://www.edx.org/course/sharepoint-basics-it-professionals-microsoft-cld202x.
Today one of my users encountered the 5,000 item limit. After speaking with him, I found out they have no need to retain items in the list more than 90 days old. The simple solution was to delete everything that was more than 90 days old and then set a retention policy to dump things in the recycle bin after 90 days. Now they'll never run into the problem again (unless their normal daily volume increases substantially).
Marc D Anderson commented
Many times the only reason we need 5000+ items is to do some counts or sums. If those capabilities were there, it would cover a lot of ground. Other times we simply need more than 5000 items. Period.
This has always felt like an artificial limitation. In SharePoint 2007, there's no limit and I've built applications that retrieve 20000, 30000+ items using the SOAP services because I truly need to. This is the second decade of the 21st century. If the limit was 100,000 it might make more sense. 5000 is simply way too low. This is especially true since you guys tell us that lists con contain millions of items. If we stuff millions of items into a list and can't retrieve them, then it really defeats the purpose.
Martinus Hamers commented
Big Throttling issues here with Project Online! Need solution!
Maybe there should be more focus on a solid setup instead of launching new features and apps every day!