SQL-DMO Examples: Indexes

SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.

This example illustrates using SQL Distributed Management Objects (SQL-DMO) to create a unique, nonclustered index on a Microsoft SQL Server table.

The IndexedColumns property, a write-only property, is used to specify columns participating in a SQL Server index when the index is created. The IndexedColumns property value uses the SQL-DMO multistring data type. Column name identifiers in the string are quoted by using the bracket characters ([]). If more than one column is specified, separate column identifiers using a comma, as in: [OrderID],[ProductID].

' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Dim tableProducts As SQLDMO.Table

Set tableProducts = _ 
    oSQLServer.Databases("Northwind").Tables("Products")

' Create a new Index object, then populate the object defining a 
' unique, nonclustered index on the indicated filegroup.
Dim idxProductName As New SQLDMO.Index
idxProductName.Name = "idx_Products_ProductName"
idxProductName.FileGroup = "fgNorthwindIdx"
idxProductName.Type = SQLDMOIndex_Unique
idxProductName.IndexedColumns = "[ProductName]"

' Create the index by adding the populated Index object to its
' containing collection.
tableProducts.Indexes.Add idxProductName