SharePoint: Page through SharePoint lists

Update Nov 27, 2014: Just posted this YouTube video that shows how to easily identify top SharePoint Performance Problems: SharePoint Performance Analysis in 15 Minutes

SharePoint lists can contain thousands of items. We have all heard about the 2000 items limit that should not be exceeded in order to achieve good list performance. There are definitely performance impacts when exceeding this limit and there are ways to overcome this limit by using indexed columns and views. Besides these considerations its also important to be smart when accessing data in the list. As already explained in the previous post – only accessing the data that you need can take a lot of pressure of the SharePoint Content Database. Additionally to that the SharePoint Object Model provides additional features to enhance access to list items.

Scenario 4: Paging through SharePoint list items with SPQuery

Paging data is a technique that we all well know from rich client applications or from web applications using e.g.: data grids. Paging allows easy navigation for the end-user and – if implemented correctly – reduces the load on the underlying database.

The SPQuery object provides the property ListItemCollectionPosition that allows you to specify the start position of your query page. RowLimit allows you to specify how many items to retrieve per page. Lets have a look at some sample code:

SPQuery query = new SPQuery();
query.RowLimit = 10; // that is our page size
  SPListItemCollection items = SPContext.Current.List.GetItems(query);
  // do something with the page result

  // set the position cursor for the next iteration
  query.ListItemCollectionPosition = items.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null)

SPList.GetItems takes the query and only returns 10 items each time GetItems is called. The SPListItemCollection offers the property ListItemCollectionPosition which acts like a cursor on the SharePoint list. This property can be used for any further page iteration to define the starting point of the next page. Using Dynatrace we can see what happens on the database:

Every GetItems call retrieves the number of items for the page
Every GetItems call retrieves RowItem + 1 items for the page

Looking closer at one of the SQL Statements shows us that the combination of the SELECT TOP and WHERE clause are used to retrieve the items of a certain page:

WHERE clause filters based on unique item id of last data page
WHERE clause filters based on unique item id of the last item of the previous data page


Data Paging is built into SharePoint’s SPQuery object. Its a useful feature that you can use to retrieve batches of data instead of retrieving all items of a list. Consider using this option in order to minimize the activity on the database. Its enough to just request the data that you need and its best to let that work done by the database.

Andreas Grabner has 20+ years of experience as a software developer, tester and architect and is an advocate for high-performing cloud scale applications. He is a regular contributor to the DevOps community, a frequent speaker at technology conferences and regularly publishes articles on You can follow him on Twitter: @grabnerandi