Summary of some column type behaviours with the List View Threshold

This is a summary of the behavior of the List View Threshold (LVT) with choice, lookup, managed metadata and person and group columns.

  • The columns are indexed where possible.
  • The List View Threshold is at 5,000.
  • For each type of column there is a filtered view named Red that contains around 50 files and there is a filtred view named Green that contains around 6,000 files.
  • Tested in SharePoint Online in September and October 2016.

My biggest discovery was that the filtered view displays differently when the metadata navigation is activated.

Below, I’ve highlighted some unexpected behaviours in yellow. I also include some links towards my YouTube videos.

Column Type Values Can be indexed Number of files Filtered View Metadata Navigation can be used Filtered View with Metadata Navigation Metadata Navigation
Choice Unique Yes Red: 50 OK Yes OK OK
Green: 6000 LVT Error Fall Back Query Fall Back Query
Multiple No Red: 50 LVT Error Yes (key filters only) Fall Back Query Fall Back Query
Green: 6000 LVT Error Fall Back Query Fall Back Query
Lookup Unique Yes Red: 50 LVT Error No Fall Back Query Impossible
Green: 6000 LVT Error Fall Back Query Impossible
Multiple No Red: 50 LVT Error Fall Back Query Impossible
Green: 6000 LVT Error Fall Back Query Impossible
Managed Metadata Unique Yes Red: 50 LVT Error Yes Fall Back Query OK
Green: 6000 LVT Error Fall Back Query Fall Back Query
Multiple No Red: 50 LVT Error Fall Back Query OK
Green: 6000 LVT Error Fall Back Query Fall Back Query
Person or Group Unique Yes Red: 50 LVT Error Yes (key filters only) Fall Back Query OK
Green: 6000 LVT Error Fall Back Query Fall Back Query

LVT Error: This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

Fall Back Query: Displaying only the newest results below. To view all results, narrow your query by adding a filter.

On this page you can read about more cases of the strange behaviour of the LVT:

https://ktnnsharepoint.wordpress.com/2016/08/02/big-lists-threshold/

Vote! https://sharepoint.uservoice.com/forums/282887-customer-feedback-for-sharepoint-server/suggestions/8463457-remove-the-list-view-threshold-5000-by-default