SharePoint view limit

Question

I have created an application and display data with JSOM. My problem is the limit of the view. I have set the row limit and is working fine if I get all items where ID is greater than zero, which is bringing all the items from the list. I also added a new column (indexed) and the query is failing when the second filter is giving me more than 5000 items. I really have no idea what is the difference. ID is automatically indexed, the second one was created by me. I can only guess index was not created because I have exceeded 5000 items, but as I heard, in SharePoint Online this limit is higher.

to the contrary...the limit is fixed in SPO...but configurable on prem - sharepoint.stackexchange.com/questions/114331/… — Ctznkane525
... and a more recent link from Microsoft docs.microsoft.com/en-us/sharepoint/troubleshoot/…. - But why would you need to return more than 5000 items in a list view? Maybe you need to step back and re-think your approach. — teylyn

Cornelius J. van Dyk Cornelius J. van Dyk · Accepted Answer · 2021-02-10T23:14:32

The list item retrieval limit on SharePoint Online is 5,000 and cannot be changed. Technically, it can be changed in the same way you would increase this limit for a SharePoint on prem instance, but Microsoft is the only ones with access to those settings in SharePoint Online and they're not going to change it. This user voice request has been pending for 6 years now though it does have a "Working on it" status so maybe in the next couple of years. In the mean time, you will need to implement pagination like this:

ClientContext clientContext = new ClientContext(<YourSiteURL>);
List list = clientContext.Web.Lists.GetByTitle(<YourListTitle>);
ListItemCollectionPosition itemPosition = null;
do
{
    CamlQuery camlQuery = new CamlQuery();
    camlQuery.ListItemCollectionPosition = itemPosition;
    camlQuery.ViewXml = @"<View><ViewFields><FieldRef Name=’Title’/></ViewFields><RowLimit>5000</RowLimit></View>";
    ListItemCollection listItems = list.GetItems(camlQuery);
    clientContext.Load(listItems);
    clientContext.ExecuteQuery();
    itemPosition = listItems.ListItemCollectionPosition;
    //Now process the items
    foreach (ListItem listItem in listItems)
    {
        //Do something
    }
} while (itemPosition != null)

If your app is unable to retrieve more than 5,000 items, you need to use pagination to get all the items in a large list in chunks of 5,000 until you have all of them. The above code snippet does just that.

If you're trying to modify a view directly on the SharePoint list, those won't allow more than 5,000 items to be retrieved. It becomes doubly messy when you consider that it isn't the final result set that counts, but the total POSSIBLE number of rows since the calculation is done on the SQL Server side. In your example you're probably trying to filter by the second column you created. Since the list has more than 5,000 items, the view fails because the column you created is not an indexed auto number field like the ID field. As a result, in order to return your view SQL Server has to select ALL the rows in the list in order to sort and filter by your column. Consider this.

The internal database structure for SharePoint lists stores all rows from all lists in ONE table named AllRows.
By default, SQL Server automatically upgrades row locks to table locks when the number of row locks on a given table exceeds 5,000. This is a performance consideration from the dinosaur age when RAM was at a premium in servers.

In any large list scenario, if SQL Server was to upgrade the row lock to a table lock, it would essentially block any and all other lists in SharePoint from being updateable. For this reason, it will block any return of more than 5,000 items to the list.

You could try to add filter to your view based on the ID field e.g. ID <= 5000 for a view called "0-5000" and then ID > 5000 && ID <= 10000 for another view called "5001-10000".

Its not the greatest solution, but it's a workable one. ;-)

SharePoint view limit

1 Answers