How to create index column in SharePoint list

In this SharePoint tutorial, we will discuss what is an index column in SharePoint? Also, how to create an index column in SharePoint Online list using CSOM (.Net client object model code).

Also, we will discuss about, automatic index management SharePoint, how to enable automatic indexing in SharePoint list or document library using PowerShell.

What is indexing in SharePoint?

If you are working with large list in SharePoint, then this tutorial will be very much helpful.

Indexing in a column in a list helps in increasing the performance when we use filters in a SharePoint list. This also helps to overcome the listview threshold issue which comes when you are to retrieve more than 5000 items from a list at once.

We can index up to 20 columns per list or library. We can not index in all type of columns.

Below are the supported and unsupported column types:

Supported Column Types:

  • Single line of text
  • Choice (single value)
  • Number
  • Currency
  • Date and Time
  • Person or Group (single value)
  • Managed Metadata
  • Yes/No
  • Lookup

Unsupported Column Types:

  • Multiple lines of text
  • Choice (multi-valued)
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Person or Group (multi-valued)
  • External data

Read: SharePoint modern list view customization example

Create index in SharePoint Online list using CSOM

Here I have created a console application and connect to a SharePoint Online site to do this example.

To work with.Net managed object model code we need to add below two dlls:

  • Microsoft.SharePoint.Client.dll
  • Microsoft.SharePoint.Client.Runtime.dll

Here I have a list name as SourceList and I want to do indexing in below two columns:

  • Title
  • EmailID

Below is the full code to do indexing in a list in SharePoint Online using CSOM.

public static void CreateIndexForList(string siteURL, string listName)
{
using (ClientContext ctx = new ClientContext(siteURL))
{
ctx.AuthenticationMode = ClientAuthenticationMode.Default;
ctx.Credentials = new SharePointOnlineCredentials(GetSPOAccountName(), GetSPOSecureStringPassword());
var web = ctx.Web;
ctx.ExecuteQuery();
List list = ctx.Web.Lists.GetByTitle(listName);
ctx.Load(list);
ctx.ExecuteQuery();
string Columns = “Title|EmailID”;
string[] splitCL = Columns.Split(‘|’);
for (int j = 0; j < splitCL.Length; j++)
{
Field field = list.Fields.GetByTitle(splitCL[j].Trim());
field.Indexed = true;
field.Update();
}
ctx.ExecuteQuery();
}
}

private static SecureString GetSPOSecureStringPassword()
        {
            try
            {
                var secureString = new SecureString();
                foreach (char c in "MyPassword")
                {
                    secureString.AppendChar(c);
                }
                return secureString;
            }
            catch
            {
                throw;
            }
        }

        private static string GetSPOAccountName()
        {
            try
            {
                return "[email protected]<tenantname>@onmicrosoft.com";
            }
            catch
            {
                throw;
            }
        }

Once you run the code, it will create index in those two columns which you can see from the list settings page-> Index columns like below:

Create index in SharePoint Online list using CSOM
index column sharepoint

This is how to create an index in SharePoint list using CSOM (.Net managed object model code).

Read: SharePoint list delete title column

Automatic index management SharePoint

Now, let us see how automatic index management sharepoint or sharepoint automatic index management works in SharePoint Online or SharePoint 2016.

In SharePoint 2013 Microsoft has set a default threshold limit of 5000 items for the SharePoint list. Once the threshold limit reached, you will get an error message saying “The number of items in this list exceeds the list view threshold“.

SharePoint 2016 also has the same threshold limit of 5000 but it made few enhancements to it so that the user will not face any error even if the threshold limit reached.

Enable Automatic Index Management in SharePoint 2016

In SharePoint 2016, they introduced this new feature known as “Automatic Index Management” which works with a new timer job “Large list column index management Timer Job” to resolve the threshold issue.

Automatic Index Management setting is available for each list in SharePoint 2016. The settings can be enabled or disabled from List Settings -> Advanced Settings -> “Automatic Index Management“. By default for all list and library, the property is enabled.

sharepoint automatic index management
sharepoint automatic index management

Once this property is enabled, then the timer job roles came.

SharePoint 2016 introduced a new timer job known as “Large list column index management Timer Job” which is responsible for auto-creation of indexes when required on various views within each list in SharePoint 2016.

We can see the timer job from Central Administration -> Monitoring then click on Review job definitions which are under Timer Jobs.

automatic index management sharepoint
sharepoint 2016 automatic index management sharepoint

The above timer job runs daily and it searches for lists whose Automatic Index Management is enabled and which list exceeds 2500 items. And when it finds a list that has a view definition that could benefit from setting an index on a column, it will automatically create this index.

According to Microsoft “For example, if a view includes a filter for “WHERE A=1 AND B=2″, the Timer Job will create an index on either column A or column B. The specific choice depends on the other view definitions in the list, with the goal of minimizing the number of indexes created.”

Enable List View Auto Indexing in SharePoint Online

The same list views auto indexing also available in SharePoint Online list and libraries.

Open any SharePoint Online list of library and then open the list & library settings page. Then in the List settings page, click on “Advanced settings” which is under “General Settings” section.

In the Advanced Settings page, choose Yes in “Automatic Index Management” section like below:

List View Auto Indexing in SharePoint Online
List View Auto Indexing in SharePoint Online

This is how, we can enable automatic index management in sharepoint.

SharePoint automatic indexing using PowerShell

Let us see how to enable SharePoint automatic indexing using PowerShell.

Enable

Below is the PowerShell cmdlets to enable automatic index management in SharePoint 2016

$web = Get-SPWeb http://mypc/sites/MySP2016SiteCollection/
$list = $web.Lists["MyEmployees"]
$list.EnableManagedIndexes = $true
$list.Update()

Disable

Below is the PowerShell cmdlets to disable automatic index management in SharePoint 2016.

$web = Get-SPWeb http://mypc/sites/MySP2016SiteCollection/
$list = $web.Lists["MyEmployees"]
$list.EnableManagedIndexes = $false
$list.Update()

this field must be indexed to enforce unique values

Let us see how to fix the error, this field must be indexed to enforce unique values.

The error “This field must be indexed to enforce unique values” comes while adding a content type to list in SharePoint Online/2013/2016/2019.

Recently we have created a few site columns of type “Managed Metadata” in SharePoint, where we read the values from the SharePoint term store.

After that, we added those site columns to the content type. Then when we add those content types to any list the below error comes. The error message says: “This field must be indexed to enforce unique values”.

This field must be indexed to enforce unique values

Edit the managed metadata site column and then change the “Enforce unique values: ” to Yes like below:

this field must be indexed to enforce unique values
this field must be indexed to enforce unique values

After this, we were able to attach content type to list.

This is how we can solve sharepoint this field must be indexed to enforce unique values error.

You may like the following tutorials:

In this tutorial we learned:

  • What is indexing in SharePoint?
  • Create index in SharePoint Online list using CSOM
  • Automatic index management SharePoint
  • SharePoint automatic indexing using PowerShell
  • Fix error this field must be indexed to enforce unique values
  • >