Sharepoint 2010 Document Library Indexed Columns

[Flavie Marciniak]

Adding an index to a list or library column increases performance when you use filters. You can add indexes on up to 20 columns on a list or library. While you can add up to 20 indexes per list or library, it's recommended you add indexes to only to the most commonly used columns and on those columns which you are using to filter data both in views as well as using the column headers. Indexes add overhead to the data so use them only where they make sense to be used.

If you've exceeded the List View Threshold and been blocked, you can normally still add indexes to columns, by default, when you have fewer than 5,000 (SharePoint Server 2013) or 20,000 (SharePoint Server 2016 and SharePoint Server 2019) items in your list or library. These values are configurable on the web application level in SharePoint by the farm administrator and could be set different for your environment. In SharePoint, you can manually add an index to a list of any size.

sharepoint 2010 document library indexed columns
Download File https://3quiildestno.blogspot.com/?file=2wJ67Q



Select the name of the list or library in the navigation, or select Settings (or Site Actions in SharePoint 2010), select Site Contents (or View All Site Content in SharePoint 2010), and then click the name of the list or library.

Using folders to organize You can use folders to organize data effectively, though you should be careful not to have a query on the folder return more than the List View Threshold. For more info, see Create a folder in a list or Create a folder in a document library.

If you're blocked by the List View Threshold, but your total number of items is less than 20,000, you can add indexes to columns in SharePoint 2019. On all versions of SharePoint, you can create a filtered view with a column index to help reduce the number of results when working with large lists and libraries. Creating a filtered view with an indexed column is a two-step process: create an index for a column, and then create a view that uses the indexed column to filter the view.

Although folders are not required to use large lists and libraries, you can still use them to help organize your data and improve the efficiency of your data access. Folder creation is enabled by default in document libraries, but not in lists. For more info, see Create a folder in a list or Create a folder in a document library.

Even when the total number of items in a list or library is very large, a view of a single folder is at least as fast as a view that filters the total number of items by using an indexed column. In some scenarios, it may be possible to distribute all the items in a list or library into multiple folders such that no folder has more than 5,000 items.

An alternative way to find documents or items in a large library or list is to use the list or library's Search box to enter a keyword or phrase. SharePoint Search has its own indexing mechanisms, and it is not subject to the List View Threshold or other related limits.



Specifies the maximum number of table rows internal to the database used for a list or library item. To accommodate wide lists with many columns, each item is wrapped over several internal table rows, up to 6 rows and up to a total of 8,000 bytes (excluding attachments).

Document library has over 5K items - accessing files in the library via web services with filtering requires index on filter column or "List Item Threshold" exceeded exception is thrown. Which is fine - I can add index on custom columns but I don't see an option to add it on "Name" column.

You can enforce uniqueness on values in a list or library column, effectively creating a primary key. While previously the identity (ID) column was the only means to provide uniqueness on a list or library, Microsoft SharePoint Foundation 2010 introduces a new feature called unique column constraints, which allows you to enforce uniqueness.

Special considerations come into play whenever you copy or move items to a document library that is enforcing unique column constraints and you use the CopyTo(String) and MoveTo(String) methods on the SPFile class to perform these operations. You must be aware of this in scenarios where you move or copy a file into such a library.

Indexed columns add performance benefits in large lists when you frequently filter and query a column. An indexed column in SharePoint Online is a special type of column used to store values that can be used as search keys. Indexed columns enable users to quickly find and retrieve information by using search and filter functions.

Please note, not every column type can be indexed, Such as Multiple lines of text, Multi-valued Choice, Lookup, Hyperlink or Picture, calculated columns, Custom Columns, Multi-valued Person or Group, and External data.

There is a document library which has over 5000 items, it has passed its item view threshold. The view was reduced to show items that met certain requirements. This view now shows less than 1000 records.

The threshold item view limit is in the Central Administration but this is not something that we want to change because of performance reasons, so this would be a last option for this. Some of the columns the view is filtered by are indexed, as this was a suggestion.

SharePoint selects the first indexed column in a query, and then uses the rest of the columns to select your data. Other columns you specify in the view filter may or may not be indexed. The view does not use those indexes, even if the result of the filtered view returns less than the List View Threshold.

If you use two or more columns in the filter expression, the determining index or indexes should use an AND operator. For example, if you want to return Dogs from a large list of animals. You have an unindexed column called Species where you have Dog as a value. If you just query for Species = Dog, your query will be throttled. However, if you have an indexed column called Class, your query becomes Class = Mammals AND Species = Dog. You could also search for cats and dogs with the query Class = Mammals AND (Species = DOG OR Species = Cats). The second query selects all Mammals, and then filters to Dogs and Cats.

It all started with SharePoint 2010. To maintain the performance of a document library (and the servers behind the scenes), Microsoft introduced the 5,000 view item limit on a document library. What that meant was that while you could put more than 5,000 items in a single library, you could not surface up (display) more than 5,000 items on a view. I actually wrote a separate post on the 5,000 view item limit issue back in 2015.

So while 5,000 is no longer a hard limit, there is still a limit to how many docs a document library can handle. There is no exact number I can give you where you will get to see the errors. It depends on how you structure your library, whether or not files are organized in folders or metadata, and whether or not you sync your library to the computer.

Microsoft also recommends that users do not sync more than 300,000 files across all SharePoint document libraries/OneDrive. So the larger the library, the sooner your users will encounter the issues.

I may do a quick follow-up post to this soon to describe how columns are indexed using the metadata navigation screen at some point in the near future, and will link back to here, as it takes a little playing around to understand how it all works ?

A SharePoint List is a container with rows and columns in which data can be stored in SharePoint, similar to an Excel spreadsheet. These pieces of content are called “list items”.Update: The difference between Microsoft Lists and SharePoint lists What is a SharePoint List?"}]}," type":"Question"," id":"https://sharegate.com/blog/demystifying-the-sharepoint-list-thresholds#QuestionhasPart_FAQPage_mainEntity1","name":"What are Boundaries and Limits in SharePoint Lists?","acceptedAnswer":[" type":"Answer"," id":"https://sharegate.com/blog/demystifying-the-sharepoint-list-thresholds#QuestionhasPart_FAQPage_mainEntity1_acceptedAnswer_Answer","text":"Let’s begin by defining what we mean by boundaries and limits. As IT technicians, we’re used to jargon, but it’s a good idea to establish the terms that will be used to ensure we’re on the same page.
Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Limits are suggested guidelines that have been settled on, and are affected by system performance and testing by the product team. These can go beyond the suggested Microsoft limits but, performance-wise, there could be repercussions elsewhere.
Thresholds are restrictions within which the default value can’t be exceeded, unless that value is explicitly modified.
These sorts of numbers were only made visible from SharePoint 2010 onwards and have apparently been grafted onto SharePoint to protect users from… well, themselves.The scenario in which this sort of protection could be deemed necessary is if, for example, one user was looking to view all 20,000 items on a list, thus slowing down everyone else using the platform.If you’re new to SharePoint, I recommend you take a look at our Start to Learn SharePoint blog series. This will help you get started, and give you many pointers on how to leverage the platform to your advantage.What are Boundaries and Limits in SharePoint Lists?"]]}],"author":[" type":"Person"," id":"https://sharegate.com/blog/demystifying-the-sharepoint-list-thresholds#BlogPosting_author_Person","name":"Nathalie Jard","url":"https://sharegate.com/blog/author/nathalie-jard"],"timeRequired":"7 min ","name":"Demystifying the SharePoint List Threshold","headline":"Demystifying the SharePoint List Threshold","dateModified":"2023-01-11T17:55:19+00:00","datePublished":"2015-03-16T19:47:00+00:00","articleBody":"
Most software has limits that end-users will eventually run into. Office 365 and SharePoint are no exception to this rule, especially as far as lists and libraries are concerned.
Related: Learn how to use SharePoint Online, best practices, tips & tricks, and common issues to avoid
In this article, we’re going to explore these buffers in detail and shed some light on a few myths that have been circulating.
What is a SharePoint List?
A SharePoint List is a container with rows and columns in which data can be stored in SharePoint, similar to an Excel spreadsheet. These pieces of content are called “list items”.
Update: The difference between Microsoft Lists and SharePoint lists
What are Boundaries and Limits in SharePoint Lists?
Let’s begin by defining what we mean by boundaries and limits. As IT technicians, we’re used to jargon, but it’s a good idea to establish the terms that will be used to ensure we’re on the same page.

Boundaries are absolute limits that cannot be breached. This is a design choice by Microsoft.
Limits are suggested guidelines that have been settled on, and are affected by system performance and testing by the product team. These can go beyond the suggested Microsoft limits but, performance-wise, there could be repercussions elsewhere.
Thresholds are restrictions within which the default value can’t be exceeded, unless that value is explicitly modified.

These sorts of numbers were only made visible from SharePoint 2010 onwards and have apparently been grafted onto SharePoint to protect users from… well, themselves.
The scenario in which this sort of protection could be deemed necessary is if, for example, one user was looking to view all 20,000 items on a list, thus slowing down everyone else using the platform.
If you’re new to SharePoint, I recommend you take a look at our Start to Learn SharePoint blog series. This will help you get started, and give you many pointers on how to leverage the platform to your advantage.
Important SharePoint List Thresholds & Boundaries

So, having boldly declared that these limits are in place, the next thing to ask is: “what are they?” There are quite a few, but some of the more important restrictions to keep in mind are:
Boundaries:

File Size: 2GB per item.
Document Count: A flat count of 30,000,000 items per library (if broken up by folders and views).
Item Count: This is also limited to 30,000,000 items per list.
Bulk Operations: Users are restricted to 100 items per bulk upload.

Thresholds:

List View Threshold: 5,000 items per view.
Admin View Threshold: 5,000 items per view.
Co-author limits: 10 concurrent editors per document (although the maximum supported is 99).

For the most part, these limits will be applicable to both SharePoint Online on Office 365 & SharePoint On-Premises, but with one key difference; the list thresholds in place cannot be changed for SharePoint Online.
The restrictions in place are in effect for everyone within the tenant, and any change could impact all customers within that shared environment.
The co-author limit is a brand-new introduction that’s in place specifically for SharePoint 2013.
The 5000 Items List View Threshold

So, getting back to the SharePoint lists thresholds, the end-user limit of 5,000 is in place as an SQL throttling limit. When servicing large queries, the underlying database will resort to row level locking, in order to effectively retrieve information.
However, in instances where more than 5,000 items are being frozen, there’s a greater efficiency in locking the entire table in question. This subsequent table locking can prevent other users from accessing and manipulating that data.
How can all of this be managed? There are several options available for doing so:

Creation/utilization of Indexed Columns & filtered views
Organizing items into folders
Limiting the items on display
Editing the default view

Indexed Columns & Filtered Views

In the list/library list settings, an option exists to create a maximum of 20 indexed columns.
When you index a column, you’re asking SharePoint to make that column more accessible and serviceable in a query than other columns.
You should be aware that this prioritization comes at a cost, which is an increased overhead on the database level. Therefore, you only want to index columns that are going to be searched and filtered on often.
Which takes us to the idea of filtered views, which can be used to cut out irrelevant items within a list. To make a filtered view more effective, it’s necessary for the first column within the list to be indexed to keep the returned results below the view threshold.
Using Folders
Within SharePoint, a newly created folder also creates a new internal index within the underlying database. This means that whenever a folder is accessed, its index is used to retrieve the relevant data.
Limiting the items on display
The SQL query that retrieves data is only part of the experience with high density lists; once the data has been retrieved, it also needs to be rendered.
A quick and easy win here is to fiddle with the pagination within your views and is automatically set it to 30. This setting effectively returns results in batches relating to whatever limit you decide to set.
One suggestion is to apply a filter to the default Allitems.aspx view. This one view, left alone, can create all sorts of problems, but making changes to it as the default landing page for many of your users can also save future headaches.
SharePoint Lists Thresholds: Myths and Realities

Knowing what SharePoint and Office 365 Limits are and encountering them are two very different things.
We believe knowing how to deal with a dense, highly-populated list is extremely useful, because view limits can be reached quite quickly. Being able to manage them will make it a lot easier down the road.
Now that you’re aware of these limits and the impact they can have on your environment’s performance, a good thing would be to look at your farm’s lists to see where they’re at.
Our tool, Sharegate, provides a built-in report that does just that. This can make dealing with SharePoint list thresholds more manageable, to say the least!
Bypassing the List Thresholds

When performing a migration in Sharegate, you might encounter this kind of error message when trying to load a list or a library:
This view cannot be displayed because the number of items exceeds the list view threshold defined in your Central Administration.
You might need to set indexes on columns with which your view is filtered to be able to load this view.
When this error occurs, no items will load. However, there is still hope for a migration. You simply need to use the view selector, and select the “Built-In” view. This is a great view that we designed to allow you to load all your items, even if you have more than the designated threshold.
Another way to prevent running into the Threshold limit is to have SharePoint automatically create the indexes for you. You can do this by enabling Metadata Navigation and Filtering as a site feature, and applying it to the lists and libraries you think will eventually reach the view threshold.
Finally, it’s impossible to cover this topic without going over this last possibility, which is to simply increase the value of the List View Threshold in the SharePoint Central Administration, in order to allow the migration of your list/library with more than 5000 items.
I would not venture into this option without first understanding that it might have an important impact on Server performance. In other words, this is not recommended, but it can still allow you to migrate your list without too much trouble.

What did you think of this article?



Leave a comment
Loading comments...






Please enable JavaScript to view the comments powered by Disqus.
"}," context":"http://schema.org"," type":"Organization","logo":" type":"ImageObject","url":"https://sharegate.com/app/uploads/2019/03/logo-sharegate1.svg","height":"18","width":"139"," id":"https://sharegate.com/#ImageObject1","image":" type":"ImageObject","url":"https://sharegate.com/app/uploads/2021/04/sharegate-og_hp.png","height":"630","width":"1200"," id":"https://sharegate.com/#ImageObject","sameAs":["https://www.facebook.com/sharegatetools","https://www.instagram.com/sharegatetools/","https://www.linkedin.com/company/sharegate","https://www.twitter.com/sharegatetools","https://www.youtube.com/user/SharegateTools?sub_confirmation=1"],"disambiguatingDescription":["Save time trusting us to automate your tedious admin tasks Focus on what’s really important: creating a great Microsoft 365 experience for your users. ShareGate software does all the heavy lifting and gives you better visibility into your organization’s SharePoint and Microsoft Teams environments.","The simplest way for IT professionals to succeed with Microsoft 365. ShareGate tools make your migration to Microsoft 365 easy, simplify your SharePoint content management and administration routine, and help you manage and secure Microsoft Teams."],"description":"Intuitive cloud management software to help IT professionals. Focused on migration, management, and security of Microsoft 365/ Office 365 environments.","areaServed":"https://www.wikidata.org/wiki/Q13780930","knowsAbout":["https://www.wikidata.org/wiki/Q18833","https://www.wikidata.org/wiki/Q775811","https://www.wikidata.org/wiki/Q28406404"],"name":"ShareGate","knowsLanguage":["EN","FR"],"url":"https://sharegate.com/"," id":"http://schemaapp.com/GSoftOrganization#SharedDataItem"]" context":"https://schema.org/"," type":"BreadcrumbList","itemListElement":[" type":"ListItem","position":1,"name":"Blog","item":"https://sharegate.com/blog/#breadcrumbitem"," type":"ListItem","position":2,"name":"Demystifying the SharePoint List Threshold","item":"https://sharegate.com/blog/demystifying-the-sharepoint-list-thresholds#breadcrumbitem"]window.schema_highlighter=accountId: "GSoft/ShareGate", output: false window.MSInputMethodContext && document.documentMode && document.write('');let isPopupActive = false; font-face font-family: "Favorit";src: url(" -sharegate/assets/font/Favorit/ABCFavoritVariable.woff2") format("woff2"),url(" -sharegate/assets/font/Favorit/ABCFavoritVariable.woff") format("woff");font-weight: 1 999;font-style: normal;font-display: swap;.menu-svg-width img width: 20px;Skip to content ShareGate docsLog In ProductKey featuresProvisioningNEW
eebf2c3492