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

In this article, I have explained the basic concepts about Indexing in SharePoint lists and libraries.

Introduction

This article is the 31st part of my SharePoint series. Here are my previous articles.

  1. Learn SharePoint In Series - Part One - Introduction
  2. Learn SharePoint In Series - Part Two - Web Application
  3. Learn SharePoint In Series - Part Three - Site Collections
  4. Learn SharePoint In Series - Part Four - Subsites
  5. Learn SharePoint In Series – Part Five – SharePoint Lists
  6. Learn SharePoint In Series - Part Six - Power Of Versioning
  7. Learn SharePoint In Series - Part Seven - Lists Advanced Settings
  8. Learn SharePoint In Series - Part Eight - Validation Settings in List Settings
  9. Learn SharePoint In Series - Part Nine - Audience Targeting
  10. Learn SharePoint In Series - Part Ten - Rating Settings
  11. Learn SharePoint In Series - Part Eleven - Form Settings in List Settings
  12. Learn SharePoint In Series - Part Twelve- Save list as a template in List Setting
  13. Learn SharePoint In Series - Part Thirteen- Permission Settings in List Settings
  14. Learn SharePoint In Series - Part Fourteen- Enterprise Metadata and Keywords
  15. Learn SharePoint In Series - Part Fifteen - Retention Policy Under Information Management
  16. Learn SharePoint In Series - Part Sixteen - RSS Settings in List Setting
  17. Learn SharePoint In Series - Part Seventeen - List Columns in SharePoint
  18. Learn SharePoint In Series - Part Eighteen - Multiple Lines Of Text Column Type in SharePoint
  19. Learn SharePoint In Series - Part Nineteen - Number and Choice Column Type in SharePoint
  20. Learn SharePoint In Series - Part Twenty - Currency and Yes No Column types in SharePoint
  21. Learn SharePoint In Series - Part Twenty One - Person or Group Column types in SharePoint
  22. Learn SharePoint In Series - Part Twenty-Two - Date and Time Column types in SharePoint
  23. Learn SharePoint In Series – Part Twenty-Three – Look Up Column Type in SharePoint
  24. Learn SharePoint In Series - Part Twenty Four - Hyperlink or Picture Column Types in SharePoint
  25. Learn SharePoint In Series -Part Twenty-Five - Calculated Column in SharePoint
  26. Learn SharePoint In Series - Part Twenty-Six - Task Outcomes and External Data Column Type in SharePoint
  27. Learn SharePoint In Series - Part Twenty-Seven - Introduction to Managed Metadata Column Types
  28. Learn SharePoint In Series - Part Twenty-Eight - Introduction to Site Columns
  29. Learn SharePoint In Series - Part Twenty Nine - Editing and Deleting Site Columns
  30. Learn SharePoint In Series - Part Thirty - Column Reordering in SharePoint

In the previous article, I have explained how a user can reorder the SharePoint lists and libraries columns. Now, let’s move one more step into this series and dig into one more interesting topic, indexing. We will understand what indexing is and why we need it. In this article, I will cover the following topics,

  • Background
  • Indexing in lists and libraries
  • Creating indexes
  • Deleting indexes from columns
  • Column Supporting and Not Supporting Indexing
  • Benefits and limitations of indexing
  • Summary

Background

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.

SharePoint

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

 

SharePoint

 

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.

SharePoint

 

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.

SharePoint

 

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.

SharePoint

 

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.

SharePoint

 

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

  • Multiple lines of text
  • Choice (Multi-Valued)
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Person or Group (Multi-Valued)
  • External Data

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.