It’s Sunday evening and I’m working late, so I’m not sure…
But how long has this option been around in the SharePoint Online ‘List Advanced Settings’?
A part of me has the idea it has been there for a while, but running a Google search on this feature doesn’t return any results.
Anyway, I guess it can be useful to do a short post on it.
Why is this feature important?
SharePoint lists have never been designed to replace database tables. But SharePoint solutions grow and the result can be that you get into trouble by hitting the SharePoint 5000 list view threshold. When your company is running you’re own SharePoint Server farm, you could try to be nice to the IT Pro guys and ask to set a higher limit (like 10k items), but with SharePoint Online this isn’t possible. Setting a higher limit will Always have a negative influence on server performance, and so it’s understandable that Microsoft doesn’t want to set higher numbers.
Bill Baer (Microsoft Senior Product Manager for SharePoint) wrote a blogpost last week about “Navigating List View Thresholds in SharePoint Server 2016 IT Preview”. In this post he describes the inner workings of this feature. Basically there is a new timer job that will hunt for Lists that exceed 2500 items and when it find a list that has a view definition that could benefit from setting a index on a column, it will automatically created this index.
For example, if a view includes a filter for “WHERE A=1 AND B=2”, the Timer Job will create an index on either column A or column B. The specific choice depends on the other view definitions in the list, with the goal of minimizing the number of indexes created.
All Lists in SharePoint 2016 and Sharepoint Online are set by default to ‘automatic management of indices’. So there is nothing you have to do, it just there. This is the Office 365 Support article which describes the feature and usecase.
A 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. Creating a filtered view with an indexed column is a two-step process: create an index for a column and create a view that uses the indexed column to filter the view:
# Indexes An 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 filtered view 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
It’s a useful addition and I love the fact that it will ‘just work’…