In this article we can explore Indexed Columns which is a less-used feature in SharePoint.
Indexed Columns allows Indexing of List or Library columns. This enable the following advantages:
1. Increase speed of data retrieval
2. Improve Performance of large list or library
Indexing at list or library level will be applied to the database tables too. This helps in increasing retrieval speed especially used for the WHERE condition fields.
Improving Performance is required when the list view threshold has reached, for example a list with more than 5000 items.
Create Indexed Columns
To create indexed columns for a list or library do the following steps.
Open the List Settings Page.
Scroll down and click on the Indexed Columns link.
You will get the following page.
Click on the Create a new index link. You will get the following page.
Please note that SharePoint does not allow all column-types to be indexed. See references section for more information.
You can choose the column for indexing. For the time being, I have chosen Content Type.
The changes will be saved automatically.
You can add up to 20 indexed columns.
If you have examined SharePoint Content Database Table structure, you will be knowing that SharePoint stores a record in multiple rows in the AllItems table. This table also contains record (items) from different lists. So you might have the concern:
Adding an Index can impact other List items too!
Explanation: This case won’t happen as once a column is indexed, the data will be stored in a new table. So whenever we are adding indexed-columns, a new database-column creation happens in the background. The new database-column will be indexed and thus provides better performance.
In this article we have explored Indexed Columns.