2

What does word 'cluster' bring into definition of clustered indexes?

I know a bit about differences between clustered and non-clustered indexes, but still don't get where 'cluster' comes into play. To me 'clustering' is 'grouping', but both clustered and non-clustered use some kind of grouping internally.

NOTE: Question is about naming rather that differences between clustered and non-clustered.

Roman Gudkov
  • 3,503
  • 2
  • 20
  • 20
  • Nothing. It's just a name. Clustered indexes aren't anymore "clustered" than non-clustered indexes. – Tab Alleman Sep 29 '14 at 13:06
  • @TabAlleman your answer is a bit misleading. It sounds as though you think there is no difference between a clustered and a non-clustered index. That is not accurate. – Sean Lange Sep 29 '14 at 13:21
  • 1
    @RGudkov you should do a quick google search and research what these indexes mean in sql server. If you did you would find a number of articles explaining this. Including this one from BOL. http://msdn.microsoft.com/en-us/library/ms190457.aspx – Sean Lange Sep 29 '14 at 13:22
  • Duplicate Question [See this](http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean) – Max Sep 29 '14 at 13:29
  • @TabAlleman If that's the case than naming is very misleading. Name is supposed to help you to understand the meaning, especially in 'A' and 'non-A' case. I still think there is a reason and what to know it... – Roman Gudkov Sep 29 '14 at 15:26
  • @SeanLange I seen this page before I posted a question - it doesn't have an answer – Roman Gudkov Sep 29 '14 at 15:29
  • @Max Just read an answer you mention - it compares clustered indexes to non-clustered, but doesn't answer my question. – Roman Gudkov Sep 29 '14 at 15:31
  • Sorry RGudkov, I didn't invent the name, but you understand the difference between clustered and non-clustered. Does the word "clustered" have anything to do with that difference? I considered pretty much the same thing as Ben posted as an answer below and decided it was too much of a stretch. In IT, you may as well get used to the idea that some things have less than perfect names. – Tab Alleman Sep 29 '14 at 16:08
  • 2
    I guess if it helps maybe you can consider them ordered and nonordered indexes? Also, a clustered index is not always the actual pyhsical order of the data. It is the logical order. It would not make sense if the actual physical order had to be adjusted all the time. If you want to take a deep dive into indexes check out the stairway on ssc. It starts out simple and goes quite deep. http://www.sqlservercentral.com/stairway/72399/ – Sean Lange Sep 29 '14 at 16:22
  • 1
    This question appears to be off-topic because it is either about semantics or a duplicate of [What do Clustered and Non clustered index actually mean?](http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean) – bummi Sep 29 '14 at 17:38

2 Answers2

2

As a term of art, it's a way to distinguish the types of index. What it means, though, is that it's clustering the actual data (as opposed to just the index keys). So, when you have two sets of cluster keys that are "near" each other, their data rows are located "near" each other on the disk. The same cannot be said of non-clustered indexes.

Let's use a simple example:

Let's say I have a table of fictional characters with two columns: CharacterID and Name. If there are, say, 1 million rows in the table and the first two are (1, 'Antman') and (2, 'Zorro') and the cluster key is CharacterId, then those two rows will be close to each other.

However, if I also have a row (1000000, 'Algernon') and I have a non-clustered index on Name, then despite Antman and Algernon being close to each other in the non-clustered index, the physical rows are likely located far apart because 1 and 1000000 are far apart.

gofr1
  • 15,741
  • 11
  • 42
  • 52
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
1

Clustered indexes sort and store the data rows in the table or view based on their key values.

Nonclustered indexes have a structure separate from the data rows.

For detailed info check MSDN.

Stevan Trajkoski
  • 300
  • 2
  • 9