Wednesday, September 11, 2013

SharePoint 2010 Indexed Columns – Best Practices

The concept of indexing a column in SharePoint is somewhat same as you can index a column in database table. The internal working and methodology to use and maintain the index column is different than how it works in case of Database indexes.

In General index on a column help you locate the row or item quick and in efficient manner and avoid scanning of several other rows. And it help improve the performance of a large list or library. When you are writing a query on your List or you are creating a filtered view, you can use the index columns as filter criteria by which you can quickly retrieve the item you want.

Also as a good practices you should not end up creating several columns of your list as index columns, there has to be logical decision on that, you can take a look at the Type of the column, values of the columns, how the value of the column is used, is it used in filter criteria’s at several other places, keep in mind that each additional column index consumes extra resources in database and add some overheads on SharePoint to perform some additional operations to maintain the indexes. It is very important that you take a look at the usability of the column across all the views, filters criteria where is most commonly used as to filter the information for retrieval. And accordingly you can decided if you want to create the column as indexed column. However SharePoint has imposed some limitation on how many and on what you can create an index column. In SharePoint list or library you can index up to 20 columns.

The following table summarizes what columns can and cannot be indexed.

Column Types that can be indexed
Single line of text
Choice (single value)
Number
Currency
Date and Time
Lookup (single value)
Person or Group (single value)
Yes/No
Managed Metadata
Column Types that can NOT be indexed
Multiple lines of text
Choice (multi-valued)
Calculated
Hyperlink or Picture
Custom Columns
Lookup (multi-valued)
Person or Group (multi-valued)
External data


2 comments:

  1. You should also point out that if you use an index against a person column, a SharePoint view will only use the index if it is the first filter condition AND you use the [Me] token. I am just finding out that you cannot use a text query like 'DOMAIN\userName' or '30;#DOMAIN\userName' or even just the site collection user UID. I have run in to this myself and am furious that Microsoft blankly say that you can index this column, without mentioning that little caveat.

    ReplyDelete