List Indexes in SharePoint 2013

We know that a SharePoint database is not a true RDBMS structure, rather a big fat table with lots of information. Hence indexes in SharePoint lists are a way to optimize performance. You can index up to 20 columns to help improve the performance of a large list or library. An index on a column enables you to quickly find the rows you want based on the values in that column. Combining indexes with filtered views can quickly retrieve the items required. Any column that you define to have a unique value requires an index.

The figure below summarizes the key points about what happens behind the scenes when you access many items in a list or library.



Figure: Steps happening when accessing List/Library

• As we know, list or library data in a site collection is stored in a SQL Server database table.
• When a user requests a filtered view with column indexes, at the backend, database queries are executed to identify a subset of columns and rows and this subset is returned.
• The settings for thresholds and limits help throttle operations and balance resources for many simultaneous users.
• With elevated privileges, developers can use object model overrides to temporarily increase thresholds and limits for custom applications.

It is important to consider the following when you create and use indexed columns. Each additional column index consumes extra resources in the database and adds some overhead to every operation to maintain the index. Hence, you should add indexes only to columns that will be used actively for filtering in views on the list or library. For this a regular check should be performed to look at all the views and see which columns are used most commonly across various views for filtering to help choose the right columns to be indexed.

A list instance supports a maximum of 20 indices as shown in the figure below.



Figure : List Settings - Indexed Columns

Some SharePoint features such as Metadata navigation and filtering require indices and cannot be enabled on a list where there is no index slot remaining.

The following table summarizes what columns can and cannot be indexed.



If required, administrators can specify a dedicated time window for all users to do unlimited operations during off-peak hours.