Dear Microsoft: Remove the 5000 Item Limit for SharePoint List Views

It’s 2016. In 1993, I used Microsoft Access to process over 1,000,000 records of data to implement a product profitability model at Staples – on an IBM 486 machine.

SharePoint-List-View-ThresholdWe’ve been saddled with a 5000 item limit for views since SharePoint 2010 launched. Oddly, in SharePoint 2007, there was no such limit. When 2010 came out, with its requirements for heavier and beefier hardware, the limit showed up.

I’ve read (well, skimmed) the white papers about why this needs to be. I’ve had long conversations with people in the SharePoint Product Group about the technical debt involved and why it is a “hard” thing to fix. I’ve read the highly visited KB articles about it, like “The number of items in this list exceeds the list view threshold, which is 5000 items” error when you view a SharePoint Online list in Office 365 Enterprise.

Bill Baer (@williambaer) has posted some promising messaging about the thresholds (See: Navigating List View Thresholds in SharePoint Server 2016 IT Preview), but I still don’t see any outward evidence that the limit is going away anytime in the foreseeable future.

I think there have been mixed messages and misinterpreted messages (no blame to Bill). The auto indexing idea which was recently added to Office 365 and comes in SharePoint 2016 (I believe – but I’m not positive) was misconstrued as lifting the 5000 item limit, which it definitely doesn’t.

The 5000 item limit in the UI for lists and libraries is arguably a good thing. There is absolutely NO reason to show more than 5000 items on a view page. Ever. No one can digest that amount of data or make use of it. Views should show just enough information for people to make good decisions about what to do next. (This is a very common mistake in SharePoint information architecture.) There are times when we simply need to have a view which derives from 5000+ items, though. Remember what year it is; 5000 items is a speck, a mote, an iota of data in this Big Data era.

No 5000 item limit

When it comes to calling SharePoint’s APIs, the 5000 item limit simply has to go. It’s a crazy limit in this day and age. I don’t care about all the technical debt talk about it. The SharePoint Product Group should be able to slide a solution underneath, at least on Office 365 – but ideally in on premises versions of SharePoint as well.

That’s the whole point of APIs: you can fix stuff under the hood without breaking any “contracts”. As more and more processing moves off-SharePoint, the 5000 limit is stifling. It’s been a problem for me for years because I live on the client side. I don’t always need all 5000+ items, but I need to know stuff about them: how many items match some criteria, what the sales total is for 12,834 items, how many items fall into a set of groupings, etc.

Whatever approach the Product Group takes shouldn’t matter, as long as they uphold the API contracts. For years they have told us – very rightly – that touching the underlying SQL tables puts us in an unsupported state. As long as we’re calling the APIs, what happens under the covers doesn’t matter. In fact, most of us should care less; that’s Microsoft’s domain and we should leave it to them.

With the new SharePoint Framework (SPX?) coming along – which I think is a TREMENDOUSLY good thing – the 5000 item limit is going to be on the minds of every developer who works with SharePoint. There will be no more server side tricks to haul out to cheat your way around this stuff. Those tricks have been reduced in degrees anyway. If SharePoint is to truly be treated as a service (as I firmly believe it should) then it needs to behave like a service in 2016+, not a service in the 1990s – or earlier.

I’ve heard many people – my fellow MVPs included – say that Microsoft will never fix this. There’s no cool Marketing moment in it, no splash they can make at a conference, little to show in the UI for it. But think of the consequences if they DON’T fix it. How long can we continue to believe that we can pump millions of items into a list (which is the true capacity) only to be told that we can only retrieve fewer than 5000 of them from the client side?

Unfortunately, if we assume they won’t fix it or can’t do it right, then the SPX may be doomed, IMO. I’d prefer to believe that they know it has to be fixed and are working on it. As my friend Jeff Shuey (@shuey) is always saying “I want to believe!”


If you want Microsoft to hear your vote on this idea, check out any or all of the feedback below ion the SharePoint UserVoice site:

  1. Remove 5000 item limit
  2. Provide a list limit over 5000
  3. Remove the list view threshold (5000 by default)
  4. Default notification for lists and library’s hitting the 5000 items limit
  5. Remove the limit of 5000 items as result in a view
  6. Allow new library look for libraries over 5000 items (provided the structure in managed correctly)<

Similar Posts

15 Comments

  1. I agree the 5k threshold needs to go away when calling a list from an API. Do you think the threshold would be there if Oracle was the database being used?

    1. @Mike:

      I’m assuming your question is rhetorical? In fact, it’s SQL under the covers and SQL itself is far more robust than the 5000 item limit. The issue is the legacy structure of content over SharePoint’s historical development.

      M.

  2. Hi Marc D,

    The Sharepoint DB is ISAM over SQL. Remember ISAM? Its a 1960’s technology that SQL replaced in the 80’s. There is the beginning and end of all problems in this topic.

    ISAM is fast for accessing up to, oooh, lets say, 5000 items. But after that it tanks. So in the 1980’s we went relational with Oracle SQL on PC’s, and then we got pivot tables in Excel, and then we discovered data mining with Segate Analysis, and then big data with AI search and user profiling techniques.

    So why did MS decide in 2001 to totally not use SQL Server as an RDB by dropping a slow ISAM API on top of it? Because the vast majority of web sites were only used for small record sets. And that is still the case today.

    Therefore, if you want to build Lists with millions of records, you need to create a SharePoint Hosted App (or Add-In) that connects directly to a relational (SQL) database, not a SharePoint (ISAM) Database. The 5000 limit is by design because SharePoint was designed for real-time browser based filter/sort of SMALL data sets. It doesnt scale up, by design.

    In order to “fix” this, SharePoint would have to ditch the ISAM layer. That’s never going to happen because then you loose the only benefit of ISAM – it forces the end user to keep their data sets small. Which is exactly what most SharePoint users appreciate, without even realising how much more complicated (and risky) their small projects would be trying to build an internal web site on a SQL platform. SharePoint is a fantastic RAD tool. The cost savings over any other method are extraordinary. But this is achieved by de-optimisation. If you plan to have more than 5000 items, then you need to go back to the Access/Excel/ASP.Net relational models.

    MORAL: Stop trying to use SharePoint to write SQL type DB applications. Its ISAM, not relational.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.