Tuesday, June 1, 2010

All of us read a lot of papers about indexes and forgot it quickly

The solution is to remember a real-life problem when any one asks u about indexes

A librarian had 1000s of books not arranged in any order. When anyone asks for a book the librarian takes 2 to 3 hrs to find the book. A wise man came to him in a dream and  told him to give every book a number and arrange them into shelves in ranges 100..200,300..400,……9900..10000.now anyone comes and asks for a book number the librarian will find it in seconds. The librarian was too happy, but life is not good, a man asked for a book named “Operating System Concepts” the librarian asked him the number he told I don’t know, the librarian takes 1 hour to find the book. then the librarian slept and the wise guy appeared and told him, to make a catalog for  book names arranged alphabetically and beside each one the book number on the shelf .FINE the problem solved .and he made catalogs to the authors and publishing dates and so on.


I’ll write the same story but replacing Books with (TABLE ROWS),book number with (CLUSTERED INDEX), Shelves with (DATA PAGAES) , Book name in book names catalog (Non-clustered index), the catalog itself (DATA PAGES also).



Table rows are stored physically in the hard disk into data pages according to their clustered index (usually primary key) range values , and inside each data page the data records saved arranged by their clustered index value.(this is the reason why no of clustered indexes is only one : because the data saved physically according to one factor)


If you have PRODUCT table (name of database entity should be singular) and attributes (PRODUCTID,PRODUCTNAME,PRICE,FACTORYCODE,CREATIONDATE)

All of us will create PRODUCTID as a primary key and so forth it becomes a clustered index and according to the queries made if it often asks for instance for PRODUCTNAME (the where portion on the query) then make a non-clustered index on its column. and so on.


The index values are stored into data pages created as a B+ Tree (Balanced tree) the intermediate pages contains ranges that directs the search process .the leaf pages contains the actual index values .


The non-clustered index tree like this but the leaf nodes contain (book name non-clustered index value along with clustered index value) to take it and search in the clustered index tree to take the actual data physically.


C# fast-food by:
Eng.Waleed abou-zaid
Senior Software Developer
Harf Information Technology
MCSD.Net,MCAD.Net,MCP,A+,N+ certified


IP CCTV Miami said...

nice infomation here,