Index Fundamentals Part 1

Today I write a short introduction about Indexes in Microsoft SQL Server.

What exactly is an Index? An Index is a construct like a tree that is upside down to find datarows by indexed columns. There is a root level, a intermediate level and a leaf level or a heap if there is no clustered index. Let me explain with a worldwide phonebook.
The Root Page includes a pointer to the phonebook for every country the phonebook for each country is the intermediate Page and gets a pointer for every name into the leaf page (on a clustered index) or the heap.

In example for the query:

Select name, street phone from myphonebook where country = 'America' and Name = 'Adele, John'

The root page is called for Country = America and says goto leaf 1, on leaf 1 the page says
row with name 'Adele, John' is at pointer 4001 and leafpage or heap retrieves the row with
the query attributes.

On my next post I show the difference between nonclustered and clustered indexes.