Table and Index manipulation
1) When we INSERT a record in a table, an entry gets into all its indexes whether clustered (physically sorted) or non-clustered (logically sorted through pointers).
2) When we UPDATE/DELETE a table, index also gets affected.
3) How does all this take place? Post me link(s) if it is too big to fit in here.
2) When we UPDATE/DELETE a table, index also gets affected.
3) How does all this take place? Post me link(s) if it is too big to fit in here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sheer interest. When there is a index scan on a clustered index it is quite clear since it is physically sorted. But on a non-clustered index, values are sorted in indexed column via pointers. How these pointers are reached and how do they point to the 1st, 2nd, 3rd etc. ordered values? Similarly on update/delete, how records in index are identified?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you pcelba. One last question. When index scan occurs is binary search carried out i.e. middle value is checked with sought value in WHERE and HAVING to split the relevant set to half each time? If so, what is the advantage that this binary search gives for GROUP BY and ORDER BY which are essentially the same unless grouping has a relevance for the end results?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much, momi & pcelba.
why are you asking? sheer interest or do you have a problem you are trying to understand the cause?