SQL 201 – Indexes

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!

Related posts:

1. SQL 201 – CLUSTERED vs. NONCLUSTERED Indexes
2. SQL 301 – Indexes With Included Columns
3. Partitioned Tables and Indexes
4. SQL 401 – FILLFACTOR and Indexes
5. SQL 201 – UNIQUE INDEX

About Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.