Indexes in Microsoft SQL are just like indexes in a book. They help you jump to the information you want more quickly. In Microsoft SQL you can index one or more columns in a table or view. The indexes defined can help your select statements run much more quickly… but they do have a cost, they can make inserts and updates more costly.

That’s OK, as long as you have a write fewer times than you seek system. Think data warehouses.

Creating an index in Microsoft SQL is fairly simple.

CREATE INDEX indexName ON <TableName|ViewName> (
   columnName(s)
)

I’d like to try and help you learn a standard when it comes to naming your indexes. I suggest you use:

IX_tableName__columnName

TableName can be a table name or a view name. Notice that’s two underscores between the table and column Name. Also, if your index covers more than one column, add them all (or as many as will fit) separated by a single underscore.

That way when you are looking at sysobjects, and you see a list of indexes, you will instantly recognize what the index covers.

Clustered versus Non-Clustered Indexes

Before we go any further I’d like to discuss the terms clustered index and non-clustered index.

A clustered index physically orders the data in your table by sorting your columns. A non-clustered index creates an separate table with the columns you’re indexing along with a pointer to the data in the base table.

You can only identify one clustered index on a table. Think about it, can you physically write the alphabet in two orders at the same time?

A clustered index can slow down your inserts if the data being inserted has values for the indexed columns that comes in a “random” order. Each row being inserted would have to seek where to write, then a row would have to be allocated, then insert the new record, then re-order the table… Lot’s of cost there.

Oh, and if you create a clustered index, I suggest changing the prefix of the name to CIX, to identify that index as being clustered.

But don’t think non-clustered is a silver bullet. If you have a large table, your index will be large too. Writing to the non-clustered index could take as long.

Deleting Indexes

When creating indexes, you’ll need to know how to remove them, especially for testing.

DROP INDEX indexName ON tableName

Now that you’ve learned indexes, you’re opening up to a whole new level of database understanding. You can now learn about partitioning; you can now learn about optimizations. There is so much more to learn now that you’ve gotten this far.

If you have any questions, send them in! I’m here to help!