20 Indexed column SharePoint – How does the database column indexing work in SharePoint for better performance?

By SP Doctor in Articles, Column Indexing, Document Library, Office 365, SharePoint, Sharepoint Lists, Sharepoint Lists and Libraries on October 2, 2019September 4, 2022

20 Indexed column SharePoint – How does the database column indexing work in SharePoint for better performance? Being a SharePoint developer, administrator, and architect, we quite often discuss the SharePoint list items loading issue and when it comes to SharePoint “list” items loading performance issue, we straight away go for indexing the columns to speed up the performance. So in this article, I will focus on what exactly happens in the SQL database when we index a column.

Table of Contents

How does SharePoint store the list of items? Indexed column SharePoint

Before we head on to indexing, we must know the SharePoint list item data structure. This will help the SharePoint architect while designing the application. The SharePoint database is a heavy database designed by Microsoft, it has a complex data structure in the back end. So being a developer or administrator we should never directly talk to the SharePoint database in fact not recommended by Microsoft, for any changes we must go with the SharePoint UI or PowerShell or by any other coding.

There is a single table, i.e. “AllUserData” in all content databases that stores all SharePoint list items I mean this is a common table for all the lists in the site collection. There are lots of internal columns in the table than the number of columns we create in the SharePoint list – so what we see in the list is just a few columns. For example 64 nvarchar, 16 units, 12 floats, and so on.

If we look at the Microsoft documentation on the “AllUserData” table structure, we will be surprised to see how complex it is. Please click here to know more about “AllUserData” table structure.

AllUserTable - Indexed column SharePoint

In my opinion, this is one of the major reasons for being slow. Had Microsoft been designed this way – for each list/library there should be a dedicated table, then the speed might have been better. Anyway, we need to move on to whatever we have.

What is indexing in the database table (What is index in database table)?

A database index is a data structure that improves the speed of data retrieval operations from a database table and it works based on the key-value pair technique – the core binary search and linear search algorithms are built on the indexing technique.

In other words, we can say – an index is a copy of selected columns of data from an original table that can be searched very efficiently, which establishes a direct link to the complete row of data from where it was copied from.

Again, there are various types of indexing in the database like cluster and non-cluster, etc. I have not been going into that since the purpose of this article is different. In a short note – indexing is a technique using which we can get the fastest query result from the database table. Indexing costs us additional writes and storage space in the file system.

Indexing structure in dbms - Indexed column SharePoint — Indexing structure in DBMS – Indexed column SharePoint

To make it more realistic, we can give an example of the indexing in the Book. Here, the main content inside the book is the main data table and indexed pages which we generally see at the beginning of each book where it mentions which content or chapter of the book is located on which page, this is just equivalent to a hash table in the database. Using the indexed page number – we can very quickly navigate to a specific chapter or page. Exactly indexing in the database table also works in a similar way.

Usually without indexing how does the data get fetched from a SQL table (Get data from SQL table without indexed column)?

Let’s write the below SQL query – this displays all purchase order details from the “Purchase Order” table where “OrderName” is ‘PO001_Laptop‘. If there are 10,000 items (rows) in the SQL table, this query will traverse thru the 10,000 rows one by one to check the below condition, even though the “PO001_Laptop” is not available in many of the rows but still, this query will scan thru all the rows.

Then, finally, it displays the result which is a time-consuming process, and hits the query performance. In this technique we cannot skip even one row, the query has to scan thru right from the first row to the last row. Imagine if we have millions of records in the database table how long would it take to complete this query?

SELECT * FROM PurchaseOrder WHERE OrderName = 'PO001_Laptop'

For the same query – if we create an index on the “OrderName” column – as per the above diagram this will create an entry in the indexing table (hash table) and the pointer will map to the original data row, whenever any query comes to SQL, first this will check the indexing table (hash table) and directly will fetch the matching items from the original data source table and will skip the checking for rest all unwanted rows where items are not matched. This is how indexing improves the performance of SQL queries.

How does the indexing column work in SharePoint?

The list items are stored in the “AllUserData” table in the SQL. For every defined indexed column SharePoint stores the index value for every list item in a separate table, i.e. “NameValuePair ” table which we have seen above. Let’s say we have 20,0000 items in the list, which means that we have 20,000 rows in “AllUserData” and 20,000 additional rows in the “NameValuePair” table (used for indexing).

So the SharePoint list items view requests are served based on the key value in the “NameValuePair” table and pick the appropriate mapping items from the main “AllUserData” table which is the actual data source. This is how the SharePoint indexing column speeds up the query performance.

How does the indexing column work in SharePoint list

Column Indexing is recommended in the SQL table for the sake of better performance.

So far we have learned what is indexing and how an indexed column improves the performance of the query. Now we need to see the other side of the coin as well – I mean is column indexing recommended?

There is no straight answer for this – it all depends on the needs and the way you operate your database table. As we have learned that column indexing costs us additional writes and storage space, so if our application requires more insert/update operations, we might want to use tables without indexes, but if it requires more data retrieval operations, we should go for indexing table.

Maximum index column in SharePoint list – Why SharePoint is limited to having 20 indexed columns in a list?

There might be other reasons, but this is my analysis. As we have seen that the drawback of an indexed column is that – the indexed column indices require additional space on the disk as the indices are stored together in a table using the MyISAM engine, this file may rapidly reach the threshold limits of the given file system means will exhaust all disk space when many columns from the same list are indexed. So we should try to minimize the indexed column number as much as possible.

What are the SharePoint columns types that are supported/unsupported for indexing?

Below are the supported and unsupported columns for indexing in SharePoint:

SUPPORTED COLUMN TYPES

Title (but not in a document library)
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)

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

Summary – what do we have here (Indexed column SharePoint)?

Thus, in this article, we have learned what exactly column indexing is in the database and how it improves the performance of SQL queries or SharePoint list view queries.

Download SharePoint Online & Office 365 Administration eBook

Buy the premium version of SharePoint Online & Office 365 administration eBook from here:

Buy SharePoint Online & Office 365 Administration eBook

Get the free demo PDF eBook from here:

FREE DOWNLOAD

Send download link to:

About Post Author

SP Doctor

I am your SharePoint and Power Platform doctor and guide. Sharing knowledge with my fellow colleagues, friends, and the tech community makes me utmost happy in my job.

See author's posts

Tags: Add an index to a SharePoint column - SharePoint, how to overcome sharepoint 5000 item limit threshold, Indexed columns, Microsoft Technology Articles, sharepoint column indexing, sharepoint indexed columns explained, SharePoint Online