SQL-DMO Examples: Indexes

This example illustrates using 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

See Also