In SharePoint, the threshold problem has been an impending problem for more than a decade. To solve this problem, we need to first understand what indexing is in the database and how the index works in SharePoint, then see how to create a column. indexing, then learn how to create a dynamic filter column with the function of today … …. so please browse these articles where you will explain the concept of indexing and how to solve this problem threshold.
What is database indexing in SharePoint?
A database index is a data structure that improves the speed of data retrieval operations from a database table and it works on the basis of the key value pair technique – algorithms Basic binary search and linear search 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 links directly to the full row of data from where it was copied. Again, there are different types of indexing in the database like cluster and noncluster etc. I did not go in this direction since the aim of this article is different. In a short note – indexing is a technique to get the fastest query result from the database table. Indexing costs us additional writes and storage space in the file system.
In SharePoint, there is only one table, that is to say "AllUserData"In all content databases that store all of the items in the SharePoint list, I mean that this is a common table for all lists in the site collection. there are a lot of internal columns in the table than what the number of columns we create in the SharePoint list – so what we see in the list are 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”Structure of the table, we will be surprised how complex it is. Please click here to learn more about "AllUserData”Table structure.
How does the indexing column work in SharePoint?
The items in the list are stored in the "AllUserData”In SQL. For each defined indexed column, SharePoint stores the index value for each list item in a separate table, that is, "NameValuePair »Table that we saw above. Suppose we have 20,000 items in the list, which means we have 20,000 rows in "AllUserData" and an additional 20,000 rows in "NameValuePair”Table (used for indexing).
So requests to view SharePoint list items are processed based on the key value in the "NameValuePair"Table and choose the appropriate mapping items from the main menu"AllUserData"Table which is the actual data source. This is how the SharePoint index column accelerates query performance.
Database indexing example:
Learn more about exactly what SharePoint column indexing is:
SharePoint – O365 – How does the database column indexing work in SharePoint?
How to fix 5000 list view threshold problem:
We can increase the limit of list items from 5000 to 50,000 (fifty thousand) in on-premise SharePoint, in fact, we can turn off the list view threshold limit which is not recommended, but in SharePoint online, we have no control over it, we need to move to the defined limitation, i.e. 5000.
Here, based on my experience, I will list a few tips and tricks to avoid this problem that will work for both online and on-premises SharePoint.
Planning – before creating the list or library, analyze the data and
plan accordingly. Try to divide the data into multiple lists or libraries.
Create multiple views and add "AND"Condition in the list filter
criteria, never add "OR"Condition"OR"Condition will try
to get all the data.
Create an index column – we can only have 20 columns indexed in a data
listing. so we need to plan this thoughtfully!
We cannot create an indexed column if the list is already hitting
the list view limit.
Maintain list of archives – here we need to review the items in the list
periodically and should check whether certain items can be deleted or
archived in another list – here we can write an automation job
which will periodically move items to another archive list
In the list filtering criteria, we have to filter the list items such as
so that at any given time, the list view returns less than
5000 items using a filter on the column column technique created (please see
the explanation below).
Create a filter (today) – "number of days technique:
One of the techniques here is to create a filter on the "Created" column and use the (Today & # 39; hui) – "number of days"With a condition less than or equal to, something like below:
The logic behind the filter on the created column is – this will try to get the items that will return less than 5000 items. If this condition also returns more than 5000 articles, we need to divide this column condition created by multiple "AND"Condition with combination of another column to ensure that the filter returns less than 5000 elements as an example below – although the implementation of this technique takes a long time.
Reference URL: How to fix listview 5000 threshold error
List view threshold error(5000 items issue) in SharePoint On-Premise/SharePoint Online
Reference URL – How to create an indexed column in SharePoint?
How to create indexed column in SharePoint on-premise and online – O365?