SharePoint lists can handle only 5000 items? Does that mean I need to invest in a SQL database to handle my application data? I have more than 5000 documents in my Notes application – can I not use SharePoint? This is for SharePoint Online? There are several questions that we keep hearing about this across multiple conversations. Hence we decided to comprehensively address SharePoint List View Threshold in a FAQ session.

SharePoint List View Threshold

What is this problem?

When you open a link on SharePoint and you face the following error that crops up.

Error message:
The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.

This error message is commonly misunderstood to mean that a SharePoint list cannot hold more than 5000 items. This error is seen on

  • SHAREPOINT ONLINE Office 365 – E3 & E5
  • SHAREPOINT 2013 ON PREMISES
  • SHAREPOINT 2016 ON PREMISES

https://support.microsoft.com/en-in/help/2759051/-the-number-of-items-in-this-list-exceeds-the-list-view-threshold-error-when-you-view-a-sharepoint-online-list-in-Office-365-enterprise

Reasons

This issue occurs because SharePoint Online uses the Large List Resource Throttling feature. By default, the list view threshold is configured at 5,000 items. When the defined item limit in a list is exceeded, the message is displayed.

The issue is not necessarily with storing, but rather with displaying the information. The SharePoint 5000 item limit threshold applies to the limit of items that are displayed in a given view.

SharePoint List View Threshold:-

SharePoint List row size – 8,000 bytes per row (Max Value)

Details:

Each list or library item can only occupy 8,000 bytes in total in the database. 300 bytes are reserved, leaving 7700 bytes for end-user columns

LIBRARY

SharePoint Documents Library – 30,000,000 per library

Details:

You can create very large document libraries by nesting folders, or using standard views and site hierarchy. This value may vary depending on how documents and folders are organized, and by the type and size of documents stored.

Addressing these limits

Here is a link from Microsoft that discusses this in detail

https://support.Office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-b8588dae-9387-48c2-9248-c24122f07c59

The key is to understand the business requirement of the user and appropriately structuring the SharePoint application. These limits are usually breached in migration scenarios where you migrate data without understanding how SharePoint deals with the data. Here are some workarounds to address this issue

  1. Indexes
  2. Filter Views
  3. Items – Splitting
  4. Items – Sync
  5. Auto-Indexing List View (5000 Items Threshold)

Filtered view with a column index is not only a way to retrieve items more efficiently, but a primary method of working with large lists and libraries without getting blocked.

  • INDEXES

Index retrieves items quickly and can improve list and library performance. You can create up to 20 indexes for a list or library. Unique values require an index and the ID column is automatically indexed. Because each index adds some overhead to every database operation to maintain the index, it’s best to only add indexes for the most common or likely columns used to query the list or library.

  • FILTERED VIEWS

When you create a filtered view, make sure that the first column in the filter expression is indexed and that the single filter expression that refers to the indexed column does not exceed the List View Threshold. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes, even if the final result of the filtered view returns less than the List View Threshold. If you use two or more columns in the filter expression, use an AND operator but make sure the first column in the expression returns the lesser amount of data.

  • ITEMS- SPLITTING

Split the items into multiple lists so that no list exceeds 5,000 items.

  • ITEMS – SYNC

Create more views and sync the items to different views.

Auto Indexing View

Office 365 already uses the recently introduced “Automatic index management” to avoid hitting 5000 items threshold on a list or library view.

To help reduce blocked operations, Office 365 automatically identifies and creates an index for a public view by doing the following:

  • It Identifies lists and libraries that are approaching or have exceeded the List View Threshold.
  • For each list or library, identifies views that don’t have an index, but would benefit from having one.
  • Creates an index for those views. In some cases, two views might share the same index.

Automatic index management occurs by default but you can turn it on or off by setting the allow automatic management of indices option in the list or library advanced settings page. Changing the setting does not affect an index you have created. But if you delete the index created by automatic index management, it might be automatically created again.

Note that it might take up to 24 hours for this index to be created, and that this feature is not designed to work with private views.

Power BI / Reports

Power BI here to stay in Microsoft eco-system and it is evolving every day.  Power BI solution is designed to help business users gain insights from their data and visualize them in a self-service way.

Power BI reports can take SharePoint Online Lists as inputs. It is possible to define SharePoint application with different lists (with less than 5000 limit) and using Power BI reports to showcase the insights from the lists in multiple ways.

Here is a link on how Power BI can be connected to SharePoint Lists to generate relevant reports

https://www.netwoven.com/2016/07/analyzing-sharepoint-list-data-using-power-bi-2/

Any other questions on SharePoint List View Threshold? Leave in comment section.