My discoveries about the List View Threshold (LVT) will help you get around it, hopefully. Some of the following revelations are troublesome that’s why I added videos. So, I hope that Microsoft will have an elegant solution for these limitations soon. I usually write in French but thought it was important to share with the English community.
You can vote here to encourage Microsoft to remove the LVT: https://sharepoint.uservoice.com/forums/282887-customer-feedback-for-sharepoint-server/suggestions/8463457-remove-the-list-view-threshold-5000-by-default
In SharePoint 2016, the LVT is said to be increased. But truthfully, only an automatic index that is applied to lists or libraries is activated. As you will read below this doesn’t solve all the problems. (Library parameters > Advanced settings)
I begun my tests in SharePoint 2013 on prem but finally worked in SharePoint online (May 2016). I started with and then adapted the PowerShell code of Cameron Dwyer in order to generate libraries or lists with 10,000 items. http://camerondwyer.wordpress.com/2013/12/11/sharepoint-powershell-how-to-create-sharepoint-test-documents-in-library-folders-for-load-performance-testing/
In the examples below (unique values), I have a library with 10,000 files.
- Around 2,000 are ‘Blue’
- Around 1950 are ‘Yellow’
- Around 50 are ‘Red’
- Around 6,000 are ‘Green’
Here are my main discoveries:
1) All items View
The allitems.aspx view doesn’t display the message ‘This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.’ in libraries or lists of 10,000 items. In a library, files are sorted by name and in a list items are sorted by ID in the allitems view. If you create a new view with the same options, it will work. But as soon as you add another sorting, you will hit the LVT.
You don’t believe me, watch my video on YouTube (SharePoint Online, September 2016).
2) Group by View for totals
The group by view is really appreciated by users because it shows totals of the items per some criteria. Even if the columns are indexed or there are folders (View with Show all items without folders), it’s impossible to use a group by view on all the items. It looks like the only way to get totals would be by exporting to Excel = which is not convenient.
You want to see it, watch my video on YouTube (SharePoint Online, September 2016).
3) Open with Explorer, Content and Structure, Quick Edit Mode
When the list exceeds the LVT, you cannot use the Windows Explorer mode or Content and structure. When the list exeeds the LVT, Open with Explorer will show ‘This folder is empty.’ If those modes are important in your situation, you need to add folders. The Quick Edit Mode is also affected by the LVT.
You want to see it, watch my video on YouTube (SharePoint Online, September 2016).
4) Lookup and Person or Group Columns
You can index the Lookup column (unique value) but it will not help to overcome the LVT.
« Important: Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold does not work. Use another type of column as the primary or secondary index. » – Microsoft (I’m very upset because this paragraph was really hidden 1 or 2 years ago.)
You want a proof, watch my video on YouTube (SharePoint Online, October 2016).
The person or group column has a similar behaviour as the lookup column. Even if you index the ‘Modified by’ column, you will get the LVT error with a filtered view on less than 5000 items. That’s why you cannot filter the checked out files.
You can see it in my video on YouTube (SharePoint Online, February 2017).
5) Managed Metadata Column
Even if you index a managed metadata column (unique value), the filtered views of less than 5,000 on that field will not work. This could make you cry!
You want to despair with me, watch my video on YouTube (SharePoint Online, September 2016).
You have to activate the Metadata Navigation and then you can only use the hierarchical or key filters on the managed metadata column to filter the list or library.
You can check this in my video on YouTube (SharePoint Online, Septembre 2016).
6) Metadata Navigation and Fall back query
This is neat! When you have folders in a library and you use hierarchical or key filters on a column, it will pretend that the folders don’t exist.
You check this in my video on YouTube (SharePoint Online, December 2016).
However there is a dark side of the Metadata Navigation.
- When the Fall Back Query message ‘Displaying only the newest results below. To view all results, narrow your query by adding a filter.’ displays, an automatic filter by the biggest ID is added. Which means that what is shown is the set of 1,250 items (of a total of 10,000) most recently CREATED (and not modified).
- Only the filtered view on the choice column acts as expected.
- The group by view is also very instructive on how the Fall Back Query works. It displays 1,250 files only.
- In a case of a lookup or metadata column (indexed and unique value), if you have 30 items with the color ‘red’ in a list of 10,000, when you apply a filtered view (with the Metadata Navigation activated), it might show you only 8 of the items that were recently created (8 from those 1,250) with the message ‘Displaying only the newest results…’
Watch the strange behaviour of the Fall Back Query in my YouTube video (SharePoint Online, September 2016).
7) Multiple Values Column
You cannot index multiple values columns of choice or managed metadata. You will have the impression that the columns are indexed when you activate the library metadata navigation but in reality the index is not created and it causes an unexpected behaviour. In the example, there is a dozen of files with the choice ‘Red’.
- The key filter on ‘Red’ with the multiple value choice column will display the results from the Fall Back Query: just 2 files of the dozen.
- The key filter or hierarchical filter on ‘Red’ with the multiple value metadata column will show all the dozen files.
Watch my video on YouTube (SharePoint Online, October 2016).
8) Summary table of some behaviours
You can find on this page a summary of the behaviour of the List View Threshold with Choice, Lookup, Managed Metadata and Person and group columns .
9) Some other ones
- If a column is indexed but the filter view contains more than 5,000 items, you will get the error message of the LVT. See my video (SharePoint Online, October 2016).
- You cannot have more than 5,000 folders.
- Setting to less than 5,000 the option ‘Number of items to display’ with ‘Display items in batches of the specified size’ nor ‘Limit the total number of items returned to the specified amount’ in a view doesn’t allow to overcome the LVT. See my video (SharePoint Online, September 2016).
- You might hope that you can distribute the files in different folders and then use the view ‘Show all items without folders’ to use filtered views on an indexed column. Unfortunately you’re wrong, only the filtered views of less than 5000 will work (and it doesn’t work with lookup or person or group or managed metadata columns). See my video (SharePoint Online, January 2017).
- It is possible to index a column in a list beyond the LVT: See my video (SharePoint Online, September 2016) In this exemple, I have 10000 items and the limit for indexing is 20000. https://support.office.com/en-us/article/Add-an-index-to-a-SharePoint-column-f3f00554-b7dc-44d1-a2ed-d477eac463b0 ‘If you’ve been blocked by exceeding the List View Threshold, you can normally still add indexes to columns when you have fewer than 20,000 items in your list or library.’
- You cannot filter on the checked out files. See my video (SharePoint Online, September 2016). This is related with the Person and group column.
- In some use cases and if you trust the search, you could use a search page with refiners to filter a big Library: See my video (SharePoint Online, January 2017)
The behavior of the list view threshold is so convoluted that I recommend that you create a proof of concept when you know that you are going to exceed 5,000 items before you implement it for users.
You can check all the references at the bottom the following page or if you speak French, you can read my articles about the subject :