Indexed Columns to Improve List Performance

In this article we can explore Indexed Columns which is a less-used feature in SharePoint.

Indexed Columns

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.

image

Scroll down and click on the Indexed Columns link.

image

You will get the following page.

image

Click on the Create a new index link. You will get the following page.

image

image

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.

image

The changes will be saved automatically.

image

You can add up to 20 indexed columns.

Database Index

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.

References

http://bit.ly/1Fy3G0j

Summary

In this article we have explored Indexed Columns.

Leave a Reply

Your email address will not be published. Required fields are marked *