What are boundaries and limits in SharePoint lists?
Every time you access a list or a document library, there is a search query raised at the backend which returns all the results. For example, if there are 20,000 or more items in a list or document library and when you try to display them– you won’t be able to see more than 5000 items on a page/view and the list/library will run into the 5000 item limit threshold.
Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Limits are advised guidelines that have been agreed upon and are affected by system performance. These can go beyond the suggested Microsoft limits, but there could be consequences in the performance.
The situation in which this sort of protection can be deemed necessary is, if one user wants to view all the 20,000 items in the list, it will result in the slowdown of the platform for everybody else using the platform.
Mostly, these boundaries or limits apply to both SharePoint Online & SharePoint On-Premise users. But the difference is; in SharePoint Online, the list thresholds place cannot be changed whereas in SharePoint On-Premise, the thresholds can be altered explicitly, but it is not recommended as it might affect the server performance.
How to manage the list?
In custom pages as well as for various other reporting needs, when we access data from SharePoint lists using OData, there will be a threshold limit of 5000 records when the data is vast.
In order to overcome this issue and manage the list effectively, we need to create indexed columns.
Indexed columns and filtered views
In the list/library settings, a user can create a maximum of 20 indexed columns. When we index a column, we are requesting SharePoint to make that column more available and executable in a query when compared to other columns.
We should be aware that this prioritization comes at a cost, which is an increased overhead at the database level. Therefore, it is advisable to index columns that are going to be searched and filtered very frequently.
The concept behind the filtering of views is to exclude irrelevant items within a list. To make a filtered view more relevant, it’s required for the first column within the list to be indexed to keep the returned rows or items below the view threshold.
Steps to index columns:
1. Go to list/library settings.
2. Click on indexed column option.
Multiple ways to overcome this challenge and retrieve the data from the list.
In OData queries, generally when querying lists items without filter, then it works fine irrespective of count of items in the list. But the problem arises when we need to add a filter in OData query based on some business requirements and when the item count in the list is more than 5000 records. It gives threshold limit error.
To overcome this, we need to index columns used for filtering the results in the OData query. But, we also need to see that after filtering query over the indexed column, the result should not be greater than 5000 records else, we will have to implement more filters using indexed columns to keep count well below 5000 records. Indexing to be done in the same way as discussed above for the required columns.
Example: /_api/web/lists/getbytitle(‘’)/items?$filter=Column1 eq 2
In the above OData query, Column1 must be indexed column if the total number of items in the list is greater than 5000 records.
CSOM section – Read
While working with CSOM for SharePoint list operations, the same threshold issue appears while trying to read all the items from a SharePoint list having more than 5000 records using CAML query. Here we will have to apply CAML query batch operations in order to achieve the same.
Below is an example of reading a SharePoint List having more than 5000 records using CAML query.
To summarize, large SharePoint lists can be managed and used effectively by indexing and below are the important points to note while indexing:
- We can have a maximum of 20 indexed columns per list.
- If we want to apply additional sorting in a view, the sort column is required to be an indexed column.
- Analyze the list items and check if some of them can be removed or archived to another SharePoint list.
In case of any query regarding SharePoint Online list thresholds, add your comments below.