Creating Indexes using Management Studio

In my previous two posts, I wrote about the indexes in general and why you should create them. In this blog, I will walk through creating an index using Visual Studio Management.

Note: I have used SQL 2008 Management Studio, but the process is more or less same in SQL 2005.

Using SQL Management Studio

1. Open Management Studio and expand the database > table you want to index.

2. Select the appropriate name. I generally add a prefix to the name "idx_". Also select whether the index is a clustered or non-clustered index. (Remember, only one clustered index is allowed in a table). Check Unique checkbox if you want the index to not allow duplicate keys.

3. Click on "Add" and select the columns you want to index. As we discussed in previous post, CHAR/VARCHAR are not good candidate keys, unless you have to use them. The order is also very important. The key which will have most duplicate data should be ranked first.

4. Go to Options and adjust the settings as needed. Defaults are generally OK here but you may want to adjust fill factor. See my previous post to learn more about fill factor.

That's all there is to it. Click on OK and you are done!

As always, feel free to leave me comments!

Thank you