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?
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.
Joao Livio commented
Mark. SharePoint Lists are not a joke since you follow the good methods, like indexing, tagging, folders etc.. If you have a publishing site for publishing pages you want to use a list in order to use a specific page layout.
Simple answer is do not use lists and move to a database for all your data requirements. SharePoint lists are nothing short of a joke.
Eric Fultyn commented
List throttling the way it is implemented, while good intentioned, seems to cause more problems for end users AND administrators than it solves. If a list is over the threshold limit, it won't let you do anything without upping the threshold, rendering the list useless. When you up the limit, you defeat the purpose of having that limit in the first place. I don't know if getting rid of a limit is necessarily the answer, but it should behave more like the maximum page amount that one can request at one time, rather than the maximum total items you can have before you are blocked from doing anything. As a farm administrator and a developer, I have seen absolutely no benefit to the current implementation. My vote is to fix it or forget it.
This request was already voiced here: http://sharepoint.uservoice.com/forums/282887-customer-feedback-for-sharepoint-server/suggestions/7077672-increase-remove-throttling
Agree, we had a lot more issue to convience the client when we built Global Record Management system for them, though we used managed metadata and term base navigation to overcome the challenge