Add an Index to a SharePoint Column – PowerShell and CSOM

In this SharePoint tutorial, we will discuss on SharePoint index column. How to add an index column in SharePoint Online or SharePoint 2013/2016/2019.

What is a SharePoint index column? SharePoint Online indexing will be helpful to increase performance while retrieving records from the document library or list through filters in SharePoint Online/2013/2016. To create an index column, open the list, go to the list settings -> Indexed columns -> then click on Create a new index.

I will show you what are the supported and unsupported columns for indexing in SharePoint Online, SharePoint 2013/2016.

I will also show how to add an index to a SharePoint list or library column. How to create a simple and compound index in SharePoint 2013/2016/online.

Also, I will explain how to delete an index from SharePoint Online/2013/2016 list and library. I have also created a video tutorial on Add an Index to SharePoint List or Library Column in SharePoint Online/2013/2016.

Also, we will check how to create an indexed column in a list Programmatically using CSOM in SharePoint Online and how to create and remove indexed column using PowerShell in SharePoint.

Video Tutorial: Add an Index to SharePoint List or Library

You can check out the video tutorial on Add an Index to Column in SharePoint Online/2013/2016 List or Document Library below. If you like our videos, subscribe to our YouTube Channel.


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 the 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)
  • Number
  • Currency
  • Date and Time
  • Person or Group (single value) (Lookup)
  • Managed Metadata (Lookup)
  • Yes/No
  • Lookup (Lookup)

Unsupported Columns for Indexing in SharePoint

  • Multiple lines of text
  • Choice (multi-valued)
  • Calculated
  • 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.

We can index up to 20 columns per list or document library in SharePoint Online or SharePoint 2013/2016. We can not index in all type of columns.

Add an index to a SharePoint column

Now I will show how can you add an index column in the 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.

Create a simple or compound index in SharePoint

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.

Add Index to SharePoint Online List or Library Column
SharePoint Online Create Index Column

In the Index Columns page, click on “Create a new index” as shown in the fig below:

SharePoint 2016 add Index column
SharePoint 2016 Indexed column

Then in the Edit Index page select the Primary Column as Title as shown in the fig below:

Add Index to SharePoint Online List or Library Column
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
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
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
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 SharePoint 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 the 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/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.

Read: SharePoint 2016 List View Auto Indexing Automatic Index Management

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
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
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
delete index SharePoint online

Create an Indexed column in SharePoint Online list using PowerShell

In my SharePoint online site, I have a list name as Logging (custom list), and if I will check there is not indexed column associated with the list.

To check this, go to the List Settings page and then click on Indexed columns link down above Views section. Then you can see there are not index columns are there like below:

how to create indexed column in sharepoint list using PowerShell
how to create indexed column in SharePoint list using PowerShell

Now below is the PowerShell script to create the indexed column. Here we are first retrieving the list column and then we are setting the indexed column by using $field.Indexed = $true.

You can run and debug the PowerShell script by using Windows PowerShell ISE.

Add-Type -Path "E:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "E:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$siteUrl = “https://onlysharepoint2013.sharepoint.com/sites/Bhawana/”
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$securePassword=ConvertTo-SecureString "*****" -AsPlainText -Force
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials("bijay@onlysharepoint2013.onmicrosoft.com", $securePassword)
$web = $ctx.Web
$ctx.Load($web)
$ctx.ExecuteQuery()
$list = $ctx.Web.Lists.GetByTitle('Logging')
$field = $list.Fields.GetByTitle('Title')
$field.Indexed = $true
$field.Update()
$ctx.ExecuteQuery()

Once you execute the above script, you can see it will create one indexed column as “Title” like below:

sharepoint 2013 create list indexed column using powershell
sharepoint 2013 create list indexed column using powershell

Remove indexed column from SharePoint Online list using PowerShell

We can also easily remove indexed column from SharePoint online list using PowerShell. By using the below PowerShell script we can remove the indexed column which we have created above.

Add-Type -Path "E:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "E:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$siteUrl = “https://onlysharepoint2013.sharepoint.com/sites/Bhawana/”
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$securePassword=ConvertTo-SecureString "**********" -AsPlainText -Force
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials(“bijay@onlysharepoint2013.onmicrosoft.com”, $securePassword)
$web = $ctx.Web
$ctx.Load($web)
$ctx.ExecuteQuery()
$list = $ctx.Web.Lists.GetByTitle('Logging')
$field = $list.Fields.GetByTitle('Title')
$field.Indexed = $false
$field.Update()
$ctx.ExecuteQuery()

Once you run the above script, it will remove the indexed column from the list. You can see below:

Remove indexed column in sharepoint online list using PowerShell
Remove indexed column in sharepoint online list using PowerShell

Create an indexed column in a list Programmatically using CSOM in SharePoint Online

Now, we will see how to create an indexed column in a list Programmatically using CSOM in SharePoint Online.

We can create an index in SharePoint online list using csom .Net managed object model code in visual studio. Below code, we will try inside a console application using visual studio. SharePoint Online: create an indexed column using c#.net managed object model (csom) in SharePoint.

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

Indexing in a column in a list in SharePoint helps in increasing the performance when we use filters. This also helps to overcome the listview threshold issue.

Create Index Column in SharePoint using CSOM

Below is the full code to create index column in SharePoint online list using csom (client object model).

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 string GetSPOAccountName()
{
try
{
return ConfigurationManager.AppSettings["SPOAccount"];
}
catch
{
throw;
}
}
private static SecureString GetSPOSecureStringPassword()
{
try
{
var secureString = new SecureString();
foreach (char c in ConfigurationManager.AppSettings["SPOPassword"])
{
secureString.AppendChar(c);
}
return secureString;
}
catch
{
throw;
}
}

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

sharepoint online create indexed column
sharepoint create index list CSOM

You may like follow SharePoint Online document library tutorials:

Conclusion

I 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.

  • SharePoint index columns
  • How SharePoint index columns work exactly?
  • Supported Columns for Indexing in SharePoint
  • Unsupported Columns for Indexing in SharePoint
  • Add an index to a SharePoint column
  • Create a simple or compound index in SharePoint
  • Manage large lists and libraries in SharePoint through Indexed Columns
  • Difference between Index column and simple column in SharePoint
  • Automatic Index Management in SharePoint 2016/Online
  • Delete SharePoint Indexed Columns
  • Create an Indexed column in SharePoint Online list using PowerShell
  • Remove indexed column from SharePoint Online list using PowerShell
  • Create an indexed column in a list Programmatically using CSOM in SharePoint Online
>