INDEXES IN DETAIL IN SQL SERVER 2005
Index is a B tree like structure internally.
One table can have maximum of 1 clustured index and upto 249 non clustured index.
Leaf level in case of clustured index stored the whole data.Data is physically sorted on the basis of clustured index in case of clustured index.
Leaf pages are build first.Indexes are build from bottom to top.
Entries in the leaf pages becomes the clustured key for the non leaf level.
Index maintains the copy of the data / table.It also maintains the heap structure.
If the root is filled with entries it means we have multiple levels.
Only one root page is there per clustured index.
IMP : For each page the structure is rearranged when the insert and delete happens.
A primary key creates a unique clustured index . Creation happens internally.
If the scan happens through all the index pages it is called as Index scan. If the indexes are not available then the system has to do table scan.
You can create indexes on views also.
To create a non clustured index , you have to add a constraint to the table.
Composite index is an index for multiple columns.
Covering index is for particular query.
Note : All Clustured indexes are covering indexes.
Indexes are used for fast reterival of data that enhances the performance of the system.
The disadvantage of the indexes is you have to save them and maintain them.Also re-arrangement in case of deletions and updations.
Syntax to create index on a table
CREATE [ UNIQUE | CLUSTURED | NON CLUSTURED ] INDEX <INDEX_NAME>
ON <TABLE NAME | VIEW NAME >
WITH PAD_INDEX = _______ % [For non leaf pages]
WITH FILLFACTOR = _______ % [For leaf pages]