Indexes

[cmsbuffet]

Index could be clustered or nonclustered. Clustered comes first. But how do they work?

 

[PGO01]

SQL can find things much easier if a column is sorted.

If you index a column, SQL stores a sorted copy of that column, with pointers in each row to the real column position in the actual table.

So when you do a search on an indexed column, SQL will find the row in the sorted copy of that column, read the pointer and then go to the correct position in the actual table.

A clustered index is how the table is actually stored in the database - like a dictionary, it does not need an index because the information is stored in order. It's even quicker for SQL to find something on a clustered index because it does not need to look at a copy table, read a pointer, and then go to the correct position; it just goes straight there.

Here's a link that will probably describe things much better!

http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

 

[gmmastros]

The only thing better than a clustered index is a covering index. Explained here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-covering-indexes

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PGO01]

I don't think that a covering index is faster than a clustered index... should be the same.

It's effectively just adding columns to the index tables so that SQL doesn't have to refer back to the original table.

 

[gmmastros]

A covering index can be faster than a clustered index. This is especially true if you have a wide table (think large varchar columns).

If you read through the blog, it explains why a covering index is faster, and shows you, through the execution plan, the SQL Server will use a covering index to speed up certain queries.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom