Create and alter indexes

There are two types of indexes in SQL Server: clustered and nonclustered. The difference is that a clustered index is the actual table, which you can imagine as containing all columns of the table. A nonclustered one, in contrast contains only the columns referenced In its definition.

To take a further step, let’s discuss the question of covered and noncovered indexes. While clustered and nonclustered refers to the table itself, the question whether or not an index is covered is based on the queries. An index is considered to be covered when all columns referenced by a query is contained in it. Let’s assume we have the following query:

SELECT Column1, Column2 FROM Table

In this case, to cover this query, we need a nonclustered index with columns Column1 and Column2. To do this, you’d call:

CREATE NONCLUSTERED INDEX MyIndex ON Table (Column1, Column2);

The biggest performance gain with covered indexes is when you have a query which returns a large number of rows, compared to the table size. So when you have a query which retrieves a hundred thousand rows from a table with millions of rows, it’s considered to be a good practice. When your query returns only a hundred rows, forget covering it.

From SQL Server 2005, it is possible to add non-key columns to a nonclustered index. These are called included columns, and you can create an index with this behavior by specifying something similar:

CREATE NONCLUSTERED INDEX MyIndex ON Table(Column1) INCLUDE (Column2)
. You can have up to 1023 included columns, so don’t restrict yourself. These included columns helps you reduce complexity (technically, the number of levels in an index).

So much for nonclustered indexes, let’s head to the clustered section!

A clustered index is the table itself, so having one will remove the need of lookups. Also, when a clustered index is defined on a table, all nonclustered indexes will use that as the row pointer, instead of the row number. For this reason, it is very important to use a column with small data storage. An ideal candidate for this role is the most referenced foreign key column.

Note that there’s a key difference between the affect on the reading and writing operations, when working with indexes. Some DML statements may benefit from the presence of indexes, but sometimes a single DML statement is performed multiple times, because of badly written indexes, so be cautious, and test your indexes regularly.

Well, I’m more confused than I was when I started writing this post in the question of indexes. I think you’ll have to seek for some other material, I’ll surely do. The Training Kit isn’t so concrete on this topic.

Advertisements

Tags: , ,

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: