Posted by: Cirilo Meggiolaro | 01/2/2009

Tip of the day #80 – Create index with Microsoft.SqlServer.Management.Smo namespace

Database objects have hierarchical structure. A database belongs to a server or an instance. A table belongs to a database. A column belongs to tables and so on. You may have noticed that the Microsoft.SqlServer.Management.Smo namespace classes provide this hierarchical view as well. You will face a lot of constructor methods and properties that store the parent object with the SqlSmoObject object type.

Create indexes is not different. An index belongs to a table and may contain one or more columns, may be clustered or non-clustered and so on. Keeping this hierarchical view in mind makes your work easier.

Let’s check today two examples:

  1. Create a primary key for the table we created on Tip #79;
  2. Create an unique index for a table named order_link in two columns named customer_id and order_id.

Index class

The Index class is the main object you need to create. The following constructor overloads are available for this class:

  • public Index();
  • public Index(SqlSmoObject parent, string name);

The main properties for the index class are:

  • byte FillFactor: Gets or sets the fill factor for an index;
  • int ID: Gets the unique id for the index;
  • IndexedColumnCollection IndexedColumns: Gets a collection of indexed columns added to the index;
  • IndexKeyType IndexKeyType: Gets or sets the index type for an index. The available options are: DriPrimaryKey, DriUniqueKey and None.
  • bool IsClustered: Gets or sets a Boolean flag that specifies whether the index is clustered (true) or non-clustered (false).

IndexedColumn class

An index must have one or more columns that compound the index. The IndexedColumn defines an existing column that is part of an index or the only column in the index. The following constructor overloads are available:

  • public IndexedColumn();
  • public IndexedColumn(Index index, string name);
  • public IndexedColumn(Index index, string name, bool descending);

The properties for the IndexedColumn class are:

  • bool Descending: Gets or sets a Boolean flag that specifies whether the sorting order for the column;
  • byte ID: Read-only property that retrieves the unique indexed column id;
  • bool IsComputed: Read-only property that retrieves a Boolean flag that indicates whether the column is computed or not;
  • bool IsIncluded: Gets or sets a Boolean flag that specifies whether the column is included in the index;
  • Index Parent: Gets or sets the parent object. In this case an index object.

Primary key

To add the primary key we need to perform the following tasks:

  • Create an instance of the Server class;

Server svr = new Server(@”MYPC\SQLEXPRESS”);

  • Retrieve the database object instance passing the server object we created on the previous step and the database name;

Database db = new Database(svr, “SmoDb”);

  • Retrieve the table object instance using the Tables collection under the database object:

Table tbl = db.Tables[“Customer”];

  • Create an index object instance passing the table object and the primary key name. In this case the name will be PK_CUSTOMER;

Index primaryKey = new Index(tbl, “PK_CUSTOMER”);

  • Set the IndexKeyType as DriPrimaryKey:

primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;

  • Create an IndexedColumn object passing the index object and the column name as parameter;

IndexedColumn pkCol = new IndexedColumn(primaryKey, “customer_id”);

  • Add the indexed column to the IndexedColumns collection under the Index object;

primaryKey.IndexedColumns.Add(pkCol);

  • Invoke the Create method from the Index class:

primaryKey.Create();

The final code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

try
{
    if (svr != null)
    {
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
        {
            /// Retrieves the table instance.
            Table tbl = db.Tables[“Customer”];

            if (tbl != null)
            {
                /// Creates an index object passing the table
                /// object and index name as parameter.

                Index primaryKey = new Index(tbl, “PK_CUSTOMER”);

                /// Defines the index as primary key.
                primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;

                /// Creates an indexed column passing the index
                /// object and the column name as parameter.

                IndexedColumn pkCol = new IndexedColumn(primaryKey, “customer_id”);

                /// Adds the indexed column to the index object.
                primaryKey.IndexedColumns.Add(pkCol);

                /// Creates the primary key.
                primaryKey.Create();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Unique Index

To create an unique index in two columns named customer_id and order_id in a table named order_link is almost the same than the previous example, except by the fact that we are going to add two indexed columns to the index and set the IndexKeyType property as DriUniqueKey and set the index as non-clustered. The code is self-explanatory and must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

try
{
    if (svr != null)
    {
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
        {
            /// Retrieves the table instance.
            Table tbl = db.Tables[“order_link”];

            if (tbl != null)
            {
                /// Creates an index object passing the table
                /// object and index name as parameter.

                Index uniqueKey = new Index(tbl, “IDX_CUSTOMER_ORDER”);

                /// Defines the index properties.
                uniqueKey.IndexKeyType = IndexKeyType.DriUniqueKey;
                uniqueKey.IsClustered = false;
                uniqueKey.FillFactor = 50;

                /// Creates indexed columns passing the index
                /// object and the column name as parameter.

                IndexedColumn pkCustomerCol = new IndexedColumn(uniqueKey, “customer_id”);

                IndexedColumn pkOrderCol = new IndexedColumn(uniqueKey, “order_id”);

                /// Adds the indexed columns to the index object.
                uniqueKey.IndexedColumns.Add(pkCustomerCol);
                uniqueKey.IndexedColumns.Add(pkOrderCol);

                /// Creates the unique key.
                uniqueKey.Create();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: