Indexes - part 1

One of the most points to know that proper index means fast query retrieving, so we need to know some details about indexes in Sql server 2005 & 2008.

Definition:

You can consider the index in Database as index of book so by using index of book you look up will be faster than if you look up through each page in the book and this is the index in the database.

Scope:

You can create indexes on columns of table or view so this provides fast way to find data or values within these columns.

Example for clarification:

For example if we create a primary key "EmployeeId " and make it as indexed column so if you query by one of values of this column ,Sql server engine first finds that value in the index and then use the index to locate to entire row . vice versa if the table does not has an indexed column, Sql server query engine will look up through each row in this table till find the values and this harms the performance. So Indexes help you when you try to find specific info for example (WHERE clause …) but if you retrieving the whole table the indexes are nothing here.


Type of columns that can be indexed:

Any type of column like integer ,uniqueidentifier or xml,.. can created as indexed column except the (LOB) data type like image ,text or varchar(MAX).


B-tree

An Index is a set of pages or index nodes that organized as above structure "B-tree".

There is at the top of hierarchy structure root called "Root Level" and at the bottom of hierarchy there is root called "Leaf Level" and between these two levels the branches called "intermediate level".

Note:

"Clearly, indexing large amounts of data can significantly improve search performance. a b-tree also optimizes costly disk accesses that are of concern when dealing with large data sets."

For example if you write a query against an indexed column ,Sql server query engine first starts at the root level and move down through intermediate level until it reaches the leaf level.

So if we look for "T" value in indexed column, the query engine will first look in the root level to determine which page to reference in the top intermediate level. In this example, the first page points the values A-M, and the second page, the values N-Z, so the query engine would go to the second page on that level. Then the query engine move down to last or leaf level at the page that include "T" value. The leaf node will contain either the entire row of data or a pointer to that row, based on type of indexes whether it's clustered index or nonclustered index.