CODEWITHSUNDEEP
sqlsql-servert-sqlsmounique-index
andre
andre

Reputation: 367

How to script a unique index with included columns using SQL Server Management Objects (SMO)?

I’m trying to use SQL Server Management Objects (SMO) library to script a table creation with a unique non-clustered index that contains included columns.

The sort of script I’d like it to generate would be similar to:

CREATE TABLE [dbo].[testTable]
(
        [columnA] [int],
        [columnB] [int],
);

CREATE UNIQUE NONCLUSTERED INDEX [myIndex] 
ON [testTable] (A) INCLUDE (B)

My problem is that SMO, when it tries to script a unique index with included columns, fails with the exception below. It seems that SMO thinks that any unique index I create on a table is to be generated using the ADD CONSTRAINT statement, instead of the CREATE INDEX. I believe this is why it fails, because included columns are only valid in indexes, not on constraints. This happens on both versions 12 and 13.

Microsoft.SqlServer.Management.Smo.WrongPropertyValueException: Cannot define an included column on this index type.
at Microsoft.SqlServer.Management.Smo.Index.IndexScripter.ScriptColumn(IndexedColumn col, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Index.ConstraintScripter.ScriptColumn(IndexedColumn col, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Index.IndexScripter.ScriptColumns(StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Index.IndexScripter.GetCreateScript()
at Microsoft.SqlServer.Management.Smo.Index.GetDDL(ScriptingPreferences sp, Boolean creating, Boolean tableCreate)
at Microsoft.SqlServer.Management.Smo.Index.ScriptDdl(StringCollection queries, ScriptingPreferences sp, Boolean notEmbedded, Boolean createStatement)
at Microsoft.SqlServer.Management.Smo.Table.GeneratePkUkInCreateTable(StringBuilder sb, ScriptingPreferences sp, ICollection indexes, Boolean embedded)
at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingPreferences sp, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List1 orderedUrns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects) at Microsoft.SqlServer.Management.Smo.Table.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

This is the C# snippet I was using to debug the problem:

static void Main(string[] args)
{
        Database tmpDatabase = null;

        try
        {
            tmpDatabase = new Database(new Server("(local)"), "myTempDb");
            tmpDatabase.Create();

            // create my table
            Table table = new Table(tmpDatabase, "testTable");
            table.Columns.Add(new Column(table, "columnA", DataType.Int));
            table.Columns.Add(new Column(table, "columnB", DataType.Int));

            // create my unique index and add key and included column
            Index index = new Index(table, "myIndex") { IndexKeyType = IndexKeyType.DriUniqueKey };
            index.IndexedColumns.Add(new IndexedColumn(index, "columnA"));
            index.IndexedColumns.Add(new IndexedColumn(index, "columnB") { IsIncluded = true });
            table.Indexes.Add(index);

            try
            {
                table.Create();
                var lines = table.Script(new ScriptingOptions() { Indexes = true });
                foreach (var line in lines) 
                     Console.WriteLine(line);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);

                if (e.InnerException != null) 
                   Console.WriteLine(e.InnerException.Message);
            }

            Console.ReadKey();
        }
        finally
        {
            if (tmpDatabase != null) tmpDatabase.Drop();
        }
    }

What could I change in this code to make SMO create a unique non-clustered index with included columns?

Upvotes: 4

Views: 1359

Answers (1)

i-one
i-one

Reputation: 5120

Try to change

Index index = new Index(table, "myIndex") { IndexKeyType = IndexKeyType.DriUniqueKey };

to

Index index = new Index(table, "myIndex")
{
    IndexKeyType = IndexKeyType.None,
    IsUnique = true
};

Upvotes: 2

Related Questions