Learn SharePoint In Series - Part Thirty One - Indexing In SharePoint Columns

Imagine that you have to read books that do not contain the “Table of Contents” or “Index” section. It is still very easy to read the book, but imagine if you need to search for any topic in the book. You will have only one option to read an entire book from the beginning and find the particular topics. Now, consider another case with the same book but this time it has “Table of Contents” or “Index” in it. Now, if someone asks you to read the particular topic then in that case what you would do? You will open the Index or Table of Content and search for the particular topic there and from there you will come to know the page number and you will directly jump to that page number and reply to the person who asked you. So, if the book contains more than 5000 page then also it is very simple and less time-consuming task to find any particular piece of content as compared to the book having 200 pages without a “Table of Contents” or “Index.”

It's the same case with SQL Server Database Table. If the table contains a large amount of data and you are firing any SELECT statement then, in that case, it will take much time to find your result if you haven’t defined any indexes or Primary key to the table, while it will take much less time if you have defined the Primary key or indexes on the columns.

The same is the case with SharePoint. Consider a scenario where you have a SharePoint list and you have a large amount of data into the list and you have to filter the data based on certain columns then in that case, SharePoint takes more time to filter the records and display the result. And we know that performance is everything while surfing to any website or any application. So what is the ultimate solution? The solution is Indexing the columns. SharePoint also provides the functionality using which we can manually create indexes to the list columns. Let’s understand each and everything about indexing in this article.

Indexing in lists and libraries

Consider a SharePoint List with the name “Product” which contains ID and Title columns. Both are default columns of the list which is created by default when we create a list. Now consider a scenario.

The “Product” list contains large data. And now you are working programmatically with SharePoint List. You have to fetch the data from the list of ID columns and Title columns. And then when you will measure the performance then you will come to know that the operation in which we are filtering the data based on ID column will take much less time as compared to the operations in which we are filtering the data based on Title column. The reason is, the ID column is unique and indexing is already created by SharePoint. The ID column is the same as Primary Key in the SQL Server Database Table. While Title column does not contain any indexing by default. Same is the case with SharePoint Libraries. You can observe the difference between Indexed Columns and Non-Indexed columns only when the list contains very large data. For some data, you will never be able to judge the difference between those columns.

Creating indexes

In order to create the index on any column, please open the list setting page by opening the list, clicking on the gear icon and then clicking on List Settings link.

Under “Columns” section you will find the link with “Indexed columns”.

Now, clicking on the link will open up the Indexed column page. Here you will be able to see all the existing indexes that are created manually. Automatically created indexes are not listed here, like ID column is not listed here.

Now, if you need to create index then click on “Create a new index” link. Clicking on the link will open up the following Edit Index page.

Here, from the drop-down “Primary column for this index” select the column on which you want to create an index. Here you will find the list of all available columns which support indexing. Here not every column will be listed as not every column type is eligible to apply to index.

You can also specify “Secondary Column” index. Just as we have a composite primary key in SQL Server Database, the same concept is applied over here. Here, I have selected the “Title” column to apply the indexes and then clicked on the “Create” button.

Once you have clicked on the “Create” button, then you will see that now “Title” column is listed on the page; i.e. we have successfully created the index on “Title” column.

So, here is a way of creating indexes on any particular supporting columns.

Deleting indexes from columns

In order to Delete the indexes, open the “Indexed Columns” page and click on the column from which you want to delete the index. For our case let’s assume that we have to delete the index from Title column. So click on Title column. After clicking on Title column you will be able to see the “Delete” button on the bottom of the “Edit Index” page.

So, you have successfully deleted the indexes from Title columns.

Column Supporting and Not Supporting Indexing

As I said earlier in this article, not every column type supports indexing. There are certain columns that support indexing and some do not. Let’s discover each type.

Columns Supporting Indexes

• A single line of text
• Choice (Single valued)
• Number
• Currency
• Date and Time
• Person or Group (Single Value)
• Managed Metadata
• Yes/NO
• Lookup

Column types that do not support indexing

Benefits and limitations of indexing

Adding indexing to the lists and libraries has several advantages. Filter searching operations in a large list are fast. While if we have added more indexes to the SharePoint list it can decrease the performance if it is not configured properly. Also, for now, we can only create 20 indexes per list.

Summary

In this article, we have seen the basics of indexing columns in SharePoint lists. The process and concept are the same for the SharePoint document library. So, if you can create the indexing in SharePoint List then you can also create the indexing in SharePoint Library. We have explored how to create indexes on the columns, and how to delete the columns. If you want to read full details and full documentation on indexing SharePoint columns, then please read the below documentation.

1. Add an Index to a SharePoint Columns
2. Manage large lists and libraries in SharePoint

In this article, I have explained the basics of the indexes. The concept of indexing is too vast. You can read through the official documentation if you need to dig into the details. If you have any questions or confusion then please do comment and let me know. In the next article, I will be explaining SharePoint Views in details.