SharePoint 2010–Indexing columns in a SharePoint List

Indexing columns in a SharePoint list can substantially improve the performance of various query operations, such as queries that use the indexed column, join operations, and ordering operations. In any list, you can either index a single column or define a composite index on two columns.

How It Works

SharePoint index columns on a list are somewhat similar to indexing columns in a database table.  But in the case of SharePoint these indexes are maintained in the NameValuePair table by SharePoint instead of SQL Server.  Indexed columns are actually implemented by populating all values of the indexed columns into the NameValuePair table and that table is then joined with the AllUserData table once a query is executed that uses the indexed column as a filter.  As such, you need to carefully plan your indices for your application or feature, it should not be treated like a SQL index.  You can add huge amounts of overhead because each indexed column will be stored in the NameValuePair table, so the number of additional rows added to NameValuePair is: #IndexedColumns * #ItemsInList

For example, if you have a list with 20K rows, you add 3 indices, you have an additional 60K rows in the NameValuePair table.

Enforcing Unique Values

The ability to enforce unique column values is also a new addition to SharePoint 2010.  The unique value constraint applies only at the list instance level. So while uniqueness can be defined at the site column level, it is enforced within each list.  Because of the way the unique value constraint works, you must index any columns that enforce uniqueness. You can apply the unique value constraint to a column in three ways

• interactively through the user interface
• declaratively by setting the EnforceUniqueValues attribute in the column definition
• programmatically through the SPField class.

User Interface

When the user selects Enforce unique values and clicks OK, a warning dialog is displayed if the column is not already indexed;

The user is then given the option to automatically index the column. After a column has been set to enforce uniqueness, indexing cannot be turned off for that column.

However, you can turn off indexing for that column if you first disable enforcement of uniqueness constraints.

Declaratively in the column definition

When you are defining the column

<Field

...

EnforceUniqueValues = "TRUE" | "FALSE" 

...

></Field>

Programmatically

Use the EnforceUniqueValues property on the SPField object to set a column to require unique values. The property gets and sets a Boolean value to specify whether duplicate values are allowed; the default is to allow duplicate values, so you must explicitly set a column field property to true.

SPSite site = new SPSite("http://localhost");
SPWeb web = site.OpenWeb();

SPList custList = web.Lists["Customers"];
SPField custPhone = custList.Fields["Phone Number"];

custPhone.Indexed = true;
custPhone.EnforceUniqueValues = true;

/// You must call the Update() method 
/// when you change the EnforceUniqueValues property
custPhone.Update();

Query and Index Performance

If you are querying on a field that is not indexed, and the resulting scan encounters more items in the list than the query threshold, the query will be blocked. Set SPQuery.RowLimit to a value that is less than the query threshold. The value of ContentIterator.MaxItemsPerQuery is ensured to be less than or equal to the threshold, which is the recommended value for SharePoint Server 2010.

Composite indexes can enable you to speed up queries across related values. However, like with database indices, list indexing does incur a performance overhead. Maintaining the index adds processing to creating, updating, or deleting items from a list, and the index itself requires storage space.

A list instance only supports a maximum of 20 indices;

However, Indexing more than one column may not get the boost in performance you are expecting when using more than one indexed column in a query. Always be aware of the additional resources that are involved in indexed columns. As stated above, for every indexed column you get an additional row in NameValuePair for every list item. Additionally – modifying list items also causes the index table to be modified. You’ll want to use indexed columns sparingly because some SharePoint features require indices and cannot be enabled on a list where there is no index slot remaining (Two common ones are SharePoint 2010 eDiscovery and Information Management Policy Retention). You should choose your indexed columns carefully to maximize query performance while avoiding unnecessary overhead; Generally you only want one index column per view/query.

You can only apply unique value constraints to columns with certain data types, because some data types cannot be indexed.

Supported Column Types

• Single line of text
• Choice field (but not multichoice)
• Number
• Currency
• Date/ Time
• Lookup (but not multivalue)
• Person or Group (but not multivalue)
• Title (but not in a document library)

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