Friday, May 28, 2010


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);





    // 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;





    return index;



Powered by Blogger