Creating Indexed Column In SharePoint

Introduction

If you are not clear about what indexing actually is, I would recommend reading my previous article Overview Of SharePoint Column Indexing. When I had chosen the indexing topics - I had planned for three articles in my mind, in sequence, and this is the second one. The final one will be - in the SharePointing world, what exact problem indexing solves for the business users. So the articles will be interrelated to each other - if we miss one, it would be difficult to understand another one. In this example, I will show how to create an indexed column in SharePoint List.

Supported Column Types for Indexing

Single line of text
Choice field (but not multi choice)
Number
Currency
Date/ Time
Lookup (but not multi value)
Person or Group (but not multi value)
Title (but not in a document library)
UNSUPPORTED COLUMN TYPES
Multiple lines of text
Hyperlink/Picture
Custom Field Types
Calculated Field
Boolean (yes/no)
UI version
Checked out to
Content type ID

Unsupported Column Types for Indexing

Multiple lines of text
Hyperlink/Picture
Custom Field Types
Calculated Field
Boolean (yes/no)
UI version
Checked out to
Content type ID

How to create indexed column in SharePoint List?

Go to the list settings page in which list you want to create an index and click on "Indexed Columns" link as below,

Steps To Create Indexed Column In SharePoint

Then, we will land into the below page – here click on "Create a new index" link like below,

Then, select the column (referring to the supported columns section) which you want to index from the "Primary column for this index:" drop-down list. As an example, I have selected the "Item Name" column and clicked on the "Create" button as below,

Then, come to the "Indexed Columns" page, we can see "Item Name" indexed column is displayed here – this section was empty just before.

How to create the indexed column automatically through the list view filter?

If we add any indexed allowed columns (mentioned in the above section) in the SharePoint list view filter that particular column is indexed automatically. Let's see how this can be done.

Go to the edit mode of "All Items" view.

Then, from the "Filter" section – apply filter like below. As an example I have selected the "Quantity" column and applied filter on it, then clicked on "OK" button. Now, the "Quantity" column will be displayed in the "Indexed Column" page because it is automatically indexed when we have applied a filter on it.

Notes

Indexes are created automatically in the following two situations,

When saved views have columns used for sorting or filtering
When sorting in the modern experience

Here notes to remember,

Automatic index creation when sorting in the modern experience is limited to lists and libraries with less than 20,000 items.

Key notes to remember

In a list, maximum we can create 20 indexed column.
In a list, maximum we can create 8 Lookup type indexed column.

What is the need of creating index column?

An indexed column in SharePoint is used to improve the performances of the list or library view loading. If your list contains thousand of records, and you often query (filter or sort) by a given column, you can index that column. That way, when we display a filtered/sorted view (based on this column), SharePoint does not have to loop through all the items to decide which ones to display first, it can quickly get the items or rows it needs. - This is the beauty of the indexing concept in SharePoint.

Other than just performance - another reason for indexing a column is - if your list/library contains more than 5000 items and if your query just return a smaller result, even 1 row, we will get a list view threshold error if we do not index a column which we are querying.

Summary

Hence, in this article, we have learned the below topics with respect to creating the indexed column in SharePoint list or library,

How to create indexed column in a SharePoint list or library.
In which are the scenarios indexed column gets created automatically.
What are the limitations during creating the indexed column.
Supported and unsupported columns for SharePoint column indexing.
What is the need of creating index column?
The above points are applicable for both SharePoint on-premise and SharePoint online.

References

https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59