SharePoint index columns
Everything in a site collection is stored within a SQL database that could contain thousands of items. When you create a view with a filter for a list or document library, SharePoint needs to organize the content by finding all the list items for that list in the content database and then finding all the list items that match the filter and sort options.
To improve performance, you can create indexed columns. You may think that creating an index on every column would be wise; however, these indexes also require resources, so we should use indexing wisely. Basically, after adding an index to a column, it will increase performance while retrieving records from document library or list through filters in SharePoint Online/2013/2016.
Once you have created an index for a SharePoint list/library column, you will see a performance gain when viewing information within a list/library with a large number of items or documents.
How SharePoint index columns work exactly?
SharePoint index columns help to improve performance of the SharePoint list or document library. ieDaddy wrote an article on how SharePoint index columns.
SharePoint index columns on a list are somewhat similar to indexing columns in a database table. But in the case of SharePoint, these indexes are maintained in the NameValuePair table by SharePoint instead of SQL Server. Indexed columns are actually implemented by populating all values of the indexed columns into the NameValuePair table and that table is then joined with the AllUserData table once a query is executed that uses the indexed column as a filter. As such, you need to carefully plan your indices for your application or feature, it should not be treated as a SQL index. You can add huge amounts of overhead because each indexed column will be stored in the NameValuePair table, so the number of additional rows added to NameValuePair is: #IndexedColumns * #ItemsInList
For example, if you have a list with 20K rows, you add 3 indices, you have an additional 60K rows in the NameValuePair table.
Supported and unsupported columns for indexing SharePoint
All SharePoint column types are not supported for indexing. Below are the supported and unsupported columns for indexing in SharePoint Online/2013/2016.
Supported Columns for Indexing in SharePoint
- Single line of text
- Choice (single value)
- Date and Time
- Person or Group (single value) (Lookup)
- Managed Metadata (Lookup)
- Lookup (Lookup)
Unsupported Columns for Indexing in SharePoint
- Multiple lines of text
- Choice (multi-valued)
- Hyperlink or Picture
- Custom Columns
- Person or Group (multi-valued) (Lookup)
- External data
For example Announcements list, you can create an index on the Title column but not on the Body column.
Add an index to a SharePoint column
Now I will show how can you add an index column in SharePoint Online list or document library. The process of adding an index to columns is the same in SharePoint Online/2013/2016 also. We will see how we can create a simple index for a column as well as how we can create a compound index for a column in SharePoint.
How to create a simple or compound index
Open your SharePoint list/library in the browser and from the Ribbon click the List tab and then click List Settings. On the List Settings page, scroll to the bottom of the Columns section and click “Indexed Columns“. In the case of the Document library, you can see the Library tab and Library Settings.
SharePoint Online Create Index Column
In the Index Columns page, click on “Create a new index” as shown in the fig below:
SharePoint 2016 Indexed column
Then in the Edit Index page select the Primary Column as Title as shown in the fig below:
Create index column in SharePoint 2013
Once, the index created successfully, you can see it will appear in “Indexed Columns” like below:
Add Index to SharePoint Online List or Library Column
This way we can create a Simple index in SharePoint. We can also create a compound index in SharePoint list.
Navigate to the same Create Index column page and if you will choose both primary and secondary column for indexing, then it becomes a compound index.
Not all the columns are supported for a secondary index, only a few types will be supported. The columns which will be supported will be enabled in the “Secondary Column” dropdown list.
To create a simple index, select a column that is not a Lookup column in the Primary Column section, under Primary column for this index.
To create a compound index, select a lookup column as the primary column for this index. Use a different column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.
Here you can see below I have chosen Primary Column for Indexing and secondary column for indexing like below:
add compound index column SharePoint
Once you click on Create, it will create a compound index column in SharePoint list and the index column page looks like below:
how to create index column in SharePoint 2013
Then click on Create to create the Index column, now when you open the list next time where there will be huge items, you can see the performance improvements.
Manage large lists and libraries in SharePoint through Indexed Columns
We can store up to 30 million items in a SharePoint list, or files in a SharePoint document library. But if you are trying to retrieve more than 5000 items at once then you might may the list threshold issue.
You can create an index for columns to avoid list threshold issue.
Difference between Index column and simple column in SharePoint
SharePoint Indexed columns work in the same way as in SQL server. An indexed column in SharePoint is used to improve performances of the list or library.
If you are working on a large list, then you might need to query by using a particular column. You can create an index for those columns and this was you can also avoid list threshold issue in SharePoint.
Creating an indexed column allows SharePoint to evaluate your query in an optimized way, avoiding performance impacts on the service.
Automatic Index Management in SharePoint 2016/SharePoint Online
Index management has been done automatically by Microsoft in SharePoint Online as well as SharePoint 2016/2019. By default, there is a setting available for lists in SharePoint 2016/Online. The settings are available in List Settings -> Advanced Settings.
You do not need to create the index manually, SharePoint will create an index for columns automatically.
Delete SharePoint Indexed Columns
After creating an index for a SharePoint column, you can also delete an index for the SharePoint column.
Choose the Index column like below:
delete compound index column SharePoint online
Then in the “Edit index” page, click on the Delete button like below:
delete compound index column SharePoint online
It will ask a confirmation message “Are you sure you want to delete this index?”, Click OK and the index will be deleted from the SharePoint list.
You can see below the Index Title is not available now in the SharePoint list.
delete index SharePoint online.
Hope this article helps you understand what is an index column and how we can create an index for columns in SharePoint Online/2013/2016. I have also explained What are columns supported and unsupported for indexing in SharePoint Online. How we can create a simple and compound index column in SharePoint Online. Also, I have explained how you can delete an index column from SharePoint Online/2013/2016 list or document library.