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;

}