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

In my previous post I took a closer look into SharePoint indexed columns and tried to find out what is really going on under the hood. We’ve learned that indexed columns are actually implemented by populating all values of the indexed columns into the NameValuePair table and that this table is then JOINed with the AllUserData table once a query is executed that uses the indexed column as a filter.

The questions that came to my mind after that were

  • What if I have more than one indexed column?
  • Can I index all types of columns?
  • How is the performance when using more than one index column?

What if I have more than one indexed columns?

You can create an index for more than one column in your list. The effect is that the values of each indexed column will be stored in the NameValuePair table to enable indexing. The formula to calculate the number of additional rows in NameValuePair therefore is: #IndexedColumns * #ItemsInList
I have created several lists in my site. As you can see from the query below – one has one indexed columns, two of them have two indexed columns. The query shows the number of items stored in the index table for a certain indexed field

#Rows in NameValuePair grouped by Indexed Column
#Rows in NameValuePair grouped by Indexed Column

Can I index all types colulmns?

The Indexed Column View of your List Settings allows you to pick any column in your list to be indexed. Depending on the data type of your index columns – the values will either be managed in the table NameValuePair_{SQLCollation} or in NameValuePair. NameValuePair_{SQLCollation} is used to store string values (single line text fields and choice fields). NameValuePair is storing all other data types.

How can you verify how many items are indexed in your tables?

Execute the query that is shown above in the image. Try it once with NameValuePair_{SQLCollation} and once with NameValuePair as first table in the FROM clause.

Do I gain performance when using more than one index column?

As explained above – you can have more than one column indexed. When you create a view or use the SharePoint Object Model to filter based on more than one indexed column you might be surprised about the actual SQL statement that is executed. In my example I have a list with two indexed text columns. I created a view that uses both columns with an “IS GREATER” filter. I expected that the query uses the NameValuePair table to do all the filtering and then JOINs the AllUserData table to retrieve the filtered items. Here is the actual SQL captured with dynaTrace:

SQL Statement showing only one of the two indexed columns taken from NameValuePair
SQL Statement showing only one of the two indexed columns taken from NameValuePair

The actual executed SQL Statement shows us that only the first indexed column is taken from the NameValuePair table. The second indexed column is taken from the AllUserData table. Also worth to mention. The value for the first indexed column is passed as fixed value – the value for the second column is passed as bind.

Conclusion

Indexing more than one column is possible but you may not get the benefits out of it when using more then one indexed column in a view. Always be aware of the additional resources that are involved in indexed columns. 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.