Managing Indexes with SSMS

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart.

However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same results.

Today, I am going to continue the series of articles that will recreate the [AUTOS] sample database while demonstrating how common T-SQL tasks in my script can be done with SSMS.

I want to explore how to CREATE INDEX, DROP INDEX, and ALTER INDEX commands be duplicated using the SSMS graphical interface. Please see section 1I in the code for defining indexes.

My example will assume that the [ACTIVE].[MAKES] table has been created without any indexes.

Let’s explore some of the CREATE INDEX graphical windows.

Find the indexes node for the table in SSMS and right click to bring up the sub-menu. Select the new index option. This action will bring up the table designer in the background and the new index dialog box in the foreground.

First, we are going to work on creating the unique index. Type [UNQ_MAKER_NM] as the index name, check the unique index box and make sure the index key column is [MAKER_NM]. Clicking the OKAY button will add the index and the CANCEL button will abort the action.

Second, we need to create the primary key. This task is even easier. Select the [MAKER_ID] column in the table designer and click the primary key tool bar button.

The primary key has been created. However, our naming standard uses the column name instead of table name. Find the PK_MAKES index and right click to access the sub-menu. Select the re-name option. Correct the name right in the object explorer window.

After you are done, you should have the following indexes created.

Now, let’s explore some of the ALTER INDEX graphical windows.

There are some common weekly index tasks that you might encounter as a DBA. I will be covering three of them. These tasks can be found on the indexes node sub-menu.

The first task is to disable the index. This is good idea if you have a large, manual data load.

The second task is to rebuild a disabled or highly fragmented index.

The third task is to reorganize a slightly fragmented index.

There are many properties that can be viewed or changed on an INDEX. Right click the index name in the object explorer and select the properties option from the sub-menu.

The General page shows a read only version of the information we can enter in the new index dialog box.

The Options page has various choices that effect how a index works. I have mainly worked with the storage section. Fill factor, pad index, and sort in tempdb are some useful options.

The Storage page shows a read only version of file groups and/or partitioning that is being used.

The Fragmentation page shows read only information about the characteristics of the index.

I am mainly interested in fragmentation percentage and the forwarded records. Both will cause performance issues with you system. Other properties are interesting when you are performing indexing tuning.

The Extended properties page allows you to attach custom properties to the index. I have used this option in the past to add a description (comment) to the index definition.

Finally, let’s explore some of the DROP INDEX graphical windows.

To drop an index, find the index name in the indexes node in the object explorer. Right click the name and select the delete sub-menu option. This will bring up the delete object menu below. Click OKAY to remove the index or CANCEL to abort the operation.

In a nutshell, the indexing of tables is a major part of being a database administrator.

I have shown you a couple of the types of indexes that can be created on a table. There are many more types such as covered, filtered, spatial, column store, fulltext and/or XML to be reviewed in the future.

In short, you should have a good understanding on how to CREATE, ALTER and DROP indexes using SSMS after reading this article.