Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-03-14 : 07:04:49
|
| Hi Team,I am trying to learn Clustered Index.Before going for the question, let me list what I know about clustered index (please correct if any of the following is wrong)1) Even though, there is a clustered index on a table, the actual table does not get physically sorted. The entries in the index only get sorted.2) Under no circumstances, there will be a ‘Table Scan’ in table, if the table has a clustered index. It will be a ‘Clustered Index Scan’.3) Clustered index contains all the columns available in the table – in a sorted manner. Hence this index is inherently covering.Then1) Why should we create a ‘composite’ clustered index?2) Why ‘Key Lookup’ happens on a clustered index ? [If the index is found, why it should go somewhere else with a key lookup; the data is present in the same row, right ? ]I know, I am missing something. Please be patient to explain that.Thanks Lijo Cheeran Joseph |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-14 : 11:33:39
|
| 1) is incorrect. The clustered index *is* the table, it's not a separate object (like nonclustered indexes are). It's not physically sorted though, not necessarilyKey lookups occur *to* the clustered index, not from the cluster. They occur when SQL uses a nonclustered index that isn't covering and has to get the rest of the row from the cluster.http://www.sqlservercentral.com/articles/Indexing/68563/--Gail ShawSQL Server MVP |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-03-15 : 00:15:40
|
| Thank you, Gail. Question #2 (key lookup) is clear now. Few more questions…1) Is there any advantage of creating ‘composite’ clustered index?2) Does both clustered and non-clustered index use B-Tree?Thanks Lijo Cheeran Joseph |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-15 : 04:06:15
|
| Did you read the article I referred you to? It's a 3-part introduction to indexes (link goes to 2nd part). It certainly answers your 2nd question and discusses recommendations for the cluster from which you should be able to deduct an answer to (1)--Gail ShawSQL Server MVP |
|
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-03-15 : 06:43:05
|
quote:
you should be able to deduct an answer to (1)
I read the article but was not able to deduct the advantages of composite clustered index. (I am just a beginner  )Could you please state the inference of 'advantages of composite clustered index' ? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-15 : 08:54:40
|
| A clustered index should be narrow, unchanging, unique and ever-increasing.Composite, hence multiple columns, is seldom narrow.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Clustered Index question