What are Lists and Libraries in SharePoint?
What is a List?
A SharePoint list is essentially a structured collection of data that can be shared and managed within the SharePoint environment. Lists are designed to hold rows and columns of information similar to a database or a spreadsheet. List items can even support attachments.
What is a Library?
A Library in SharePoint is essentially a specialized type of list that is designed to store documents and files. While a list typically holds rows and columns of data similar to a database, a library's primary purpose is to manage and share documents, including their metadata, version history, and access permissions. However, fundamentally, both lists and libraries operate on the same principles of storing and organizing items within the SharePoint environment.
Going forward, to simplify our explanations, we will be referring to both lists and libraries as lists.
When is a list considered large?
For our reporting purposes, a list is considered large when it has 5000 or more items stored within it. Why 5000? SharePoint has what is referred to as, a ‘list view threshold’ that occurs when trying to view 5000 or more items within a single view of list. When you go beyond this view threshold, users will start experiencing issues and, in some cases, are not be able to view items within the list altogether.
“You can store up to 30 million items or files in a list or library, however as the number of items increases, you might notice certain operations will take longer. Filtered views of large lists have a similar experience to other lists. However, when a list view shows more than 5000 items, you might run into a list view threshold error.” Manage large lists and libraries - Microsoft Support
Potential issues experienced when working with large lists
We have broken up the issues that you are likely to experience when working with large lists into 3 areas:
- System constraints
- Performance limitations
- Negative user experience
Each of the issues highlighted will have a knock-on effect on other areas and ultimately lead to a negative user experience.
System constraints
List view threshold
SharePoint has a built-in list view threshold of 5000 items, this limits the number of items that can be displayed at any given time to 5000. This limitation is designed to protect server performance and SharePoint may block any queries that exceed this limit to avoid overloading the SQL backend.
Technically, a list can hold up to 30 million items, but querying or displaying too many items can cause timeouts or errors that would negatively impact user experience.
Indexing and Filtering challenges
To enable users to filter and sort large lists, columns need to be indexed. The default columns created in lists and libraries are automatically indexed. Custom columns that are needed for filtering and sorting will need to be manually added to the index.
If a large list’s indexing has not been configured properly, SharePoint will struggle to retrieve results quickly, leading to users seeing error messages like: “This view cannot be displayed because it exceeds the list view threshold.”
Performance limitations
Performance degradation
Working with large lists can lead to performance degradation, such a slow page loads, specifically when sorting, filtering, or grouping list items. When using custom views on large lists or web parts that pull their data from those lists, the results may take longer to render or may fail entirely.
Complex permissions
Best practice for SharePoint Online suggests that permissions are handled on a site or list-level basis and advises against managing permissions on item-level within lists.
When applying item-level permissions, SharePoint needs to evaluate the permissions for each item that the user has visible in their view, leading to longer load times and frustration.
Power Platform limitations
Power Platform apps and flows that reference large SharePoint lists may hit throttling limits or fail due to timeouts. This will also likely cause delegation warnings and errors when handling the data within your apps.
Negative User experience
All of the factors above will have a negative impact on user experience because when working with large lists, users will see a noticeable lag in performance or outright errors that prevent them from interacting with the data they need to perform their work. In addition to this, working with (and navigating) large lists can be overwhelming. There are very few scenarios where humans can practically process 5000 items without struggling. This is compounded when pagination, filtering, and search doesn't work smoothly.
Best Practices to avoid large list issues
Use list optimizations
To ensure lists work as efficiently as possible, make use of metadata, column indexing, and filtered views. By using a combination of these, you can continue to use your list, even when it is well over 5000 items.
Avoid nested permissions
Avoid breaking permission inheritance below list level (eg at folder level, or even at item level). By having well-defined roles for the list, you can implement site or list level access, ensuring a consistent user experience.
Split single large lists into multiple lists
In some cases, instead of housing all your data in a single list, it may be worth splitting out the data into multiple lists. To do this, consider the following:
- Time period – Create lists by time periods, such as years or quarters. This is useful for lists that contain logs, transactions, or historical data.
- Department or business unit – If your list contains data for multiple teams or departments and they don't need to work on the same items, creating separate lists for each of these departments can facilitate a logical breakdown of the data, with the bonus of being able to set specific permissions for each of the lists.
- Category or Type – Similar to splitting by business unit, if there are particular types of items, these can be split into multiple lists. This will also allow you to create a specific set of metadata for each type of item.
- Related lists – SharePoint does not support complex relational lists (and should not be thought of as a relational database) but this approach can be used for simple relational data such as a list of tasks with an associated document library.
SharePoint list alternatives
If you have already optimized and divided data into separate lists where feasible, but continue to encounter issues or poor performance, it may be time to explore alternative solutions such as Dataverse or SQL tables. Both Dataverse and SQL are designed to efficiently manage large volumes of data, accommodate complex relational structures, and enforce record-level security.
