Using SMO to Create an Index with Included Columns

A question that came up on the SqlDownUnder mailing list today was how to create an index using SMO and specify an included column. The documentation is extremely scarce; I could find no mention of it in MSDN. Greg Low came to the rescue by noting that the IndexedColumn class has an IsIncluded property.

This is illustrated in the following C# snippet:

public Index CreateIndex
(
    string indexName, 
    bool isClustered, 
    IndexKeyType indexKeyType,
    string[] indexColumnList,  // in index column order
    bool[]   indexDescending,  
    string[] includedColumnList
)
{
    if (indexDescending != null && indexDescending.Length != indexColumnList.Length)
    {
        throw new ArgumentOutOfRangeException(
            “Either pass indexDescending as null, or with same length as indexColumnList.”);
    }
 
    Index index = new Index
                  {
                      Name = indexName,
                      IndexKeyType = indexKeyType,
                      IsClustered = isClustered
                  };
 
    int i = 0;
    foreach (string indexColumnName in indexColumnList)
    {
        bool descending = (indexDescending != null) ? indexDescending[i] : false;
        IndexedColumn indexedColumn = new IndexedColumn(index, indexColumnName, descending);
        index.IndexedColumns.Add(indexedColumn);
        i++;
    }
 
    // Only add included columns for none primary or unique indexes
    if (includedColumnList != null && indexKeyType == IndexKeyType.None)
    {
        foreach (string includedColumnName in includedColumnList)
        {
            IndexedColumn indexedColumn = new IndexedColumn(index, includedColumnName);
            indexedColumn.IsIncluded = true;
            index.IndexedColumns.Add(indexedColumn);
        }
    }
 
    return index;
}