Index Fundamentals Part 2

Today I want to show you the difference between a clustered and a nonclustered index.

There can only be one clustered index on a table but 999 nonclustered indexes. 

A clustered index reorganizes the whole table according to the index. See in Example:

Table before clustered Index:

ID;Country

1;Brasil

2;Germany

3;America

Now we create a clustered index on Country (asc) the new logical table order is:

ID;Country

3;America

1;Brasil

2;Germany

Lets see the same Index on a nonclustered index:

Table before nonclustered Index:

ID;Country

1;Brasil

2;Germany

3;America

Table after nonclustered Index:

ID;Country

1;Brasil

2;Germany

3;America

As you can see there is no difference and this is the point between nonclustered and clustered indexes, 

the clustered index reorganizes the table in the filesystem. A nonclustered Index doesn’t do that.

In the next Topic we have a look at what attributes are the best for my index.