Allen White

One Brick at a Time

I'm reminded of the song from the Broadway musical "Barnum", where P.T.'s wife teaches him to build a city by starting with one brick at a time.

A user in the MSDN forums was trying to use SMO to create a table with indexes, and was frustrated with the exception when trying to create the index. The problem was that he hadn't yet called the Create() method on the underlying table, so there was no table on which to create the index. I pulled the following code out of examples in Books Online to provide him his answer:

        Dim srv As Server
        srv = New Server("MyServer")
        'Reference the AdventureWorks database.
        Dim db As Database
        db = srv.Databases("AdventureWorks")
        'Define a Table object variable by supplying the parent database and table name in the constructor. 
        Dim tb As Table
        tb = New Table(db, "Test_Table")
        'Add various columns to the table.
        Dim col1 As Column
        col1 = New Column(tb, "Name", DataType.NChar(50))
        col1.Collation = "Latin1_General_CI_AS"
        col1.Nullable = True
        tb.Columns.Add(col1)
        Dim col2 As Column
        col2 = New Column(tb, "ID", DataType.Int)
        col2.Identity = True
        col2.IdentitySeed = 1
        col2.IdentityIncrement = 1
        tb.Columns.Add(col2)
        Dim col3 As Column
        col3 = New Column(tb, "Value", DataType.Real)
        tb.Columns.Add(col3)
        Dim col4 As Column
        col4 = New Column(tb, "Date", DataType.DateTime)
        col4.Nullable = False
        tb.Columns.Add(col4)
        'Create the table on the instance of SQL Server.
        tb.Create()
        Dim idx As Index
        idx = New Index(tb, "TestIndex")
        'Add indexed columns to the index.
        Dim icol1 As IndexedColumn
        icol1 = New IndexedColumn(idx, "ID", True)
        idx.IndexedColumns.Add(icol1)
        idx.IndexKeyType = IndexKeyType.DriUniqueKey
        idx.IsClustered = False
        idx.FillFactor = 50
        'Create the index on the instance of SQL Server.
        idx.Create()

Once the table exists, SMO has no problem creating the index and all is well.

Allen