December 29, 2012 at 1:00 am
Colleagues,
I have a problem. Recently I ceased to understand why there can be just one clustered index for a table.
Oh I know the usual answer "because the clustered index IS the table". I still can explain why it is so - but I do not understand any more.
We can have as much indexes with included columns as we want... well... if we want a reasonable amount of them. We definitely can include ALL columns into any index. What is the difference between non-cluster unique index with all the non-key columns included, and a clustered index? Clustered index does not have any bookmarks to the table rows (because it does not need any data from the table, because no table other than index itself exists). BTW, what does the non-clustered index with all included columns need the bookmarks for? Just in case some columns will be added to the table?
So if we can remove the bookmarks from one index - why cannot we do the same with two or more? Of course we must have "main" clustered index - it's key will serve as bookmarks for all the rest indexes. But why cannot we have several "regular" clustered indexes? By "regular clustered index" I mean the index with all the data and without any bookmarks on the leaf level.
So why the table can only have one clustered index? Is it just convinience, or are there some more pressing reasons?
Will appreciate any help.
December 29, 2012 at 2:15 am
Because the table only exists once. The clustered index, as you note, is the table. Yes, you can create secondary indexes with all the columns if you want, they're not the clustered index because SQL considers them copies of the data in the table, the clustered index contains the data in the table.
The clustered index is the one whose keys are present in all nonclustered indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2012 at 2:36 am
From a conceptual point of view think of this:
In a phone directory the clustered index is LastName,FirstName, Street Address.
That being the case how can the be a clustered index on LastName,FirstName, Street Address AND a different clustered index on PhoneNumber ? It doesnt work does it, the clustered index defines the data and it can only be defined as such.
December 29, 2012 at 10:59 am
GilaMonster (12/29/2012)
Because the table only exists once.
Gail, this is just the question. I know how the things are - I do not understand why. The table only exists once - why is it so? We can have as many copies of the data in the nonclustered indexes - why the table must have a "master copy"?
If the index includes all the data - what do we need bookmarks for? What is the purpose of storing the clustered index keys on the leaf level of nonclustered index with all non-key columns included? Perhaps there is some operation which we cannot do without it? Or is it just the idea "we should have a master copy of data and cannot afford to have two or more copies of equal status"?
All that I can think of - if we add some columns to the clustered index (aka the table), the non-clustered index with included non-key columns will cease to cover the entire table. If it contains bookmarks (clustered index keys), there is no problem, it is fully functional. If it had not the bookmarks, it should be rebuild in that case.
Well but what is the problem with index rebuild?
December 29, 2012 at 11:07 am
Dave Ballantyne (12/29/2012)
From a conceptual point of view think of this:In a phone directory the clustered index is LastName,FirstName, Street Address.
That being the case how can the be a clustered index on LastName,FirstName, Street Address AND a different clustered index on PhoneNumber?
I am not so sharp. What is the problem? Why cannot a different clustered index on PhoneNumber exist? Where is the contradiction?
As I can see the concept - in the first index we have a B-tree for LastName,FirstName, Street Address, and on the leaf level we have all other column data and no bookmarks. Clustered indexes do not and cannot have bookmarks, there is no separate table to be bookmarked.
What will go wrong if we create another index (B-tree for PhoneNumber) and on the leaf-level include all other columns data and no bookmarks?
We definitely can create another index on PhoneNumber with every other column included but there just would be bookmarks (clustered index keys) on the leaf level. Why cannot we remove them and have 2 clustered indexes? What do we need them for?
December 29, 2012 at 12:46 pm
Yggaz (12/29/2012)
Gail, this is just the question. I know how the things are - I do not understand why. The table only exists once - why is it so? We can have as many copies of the data in the nonclustered indexes - why the table must have a "master copy"?
There has to be an authoritative, master copy of the data. If the table existed twice, which is the real one? Which one can be dropped and recreated from the other? Which one, when dropped, changes all the nonclustered indexes? Nonclustered indexes always have the clustered index key in them. If there were two or more clustered indexes, which key values go into the nonclustered indexes? Can't be both, that would waste space
If you want the table stored twice, then do so. Create table or create nonclustered indexes that duplicate the table. Both cases you must explicitly choose to duplicate the table.
If the index includes all the data - what do we need bookmarks for?
What happens when someone adds a column?
What is the purpose of storing the clustered index keys on the leaf level of nonclustered index with all non-key columns included?
Because SQL cannot possibly guarantee that a nonclustered index does indeed have all the non-key columns always. The cluster, because it is the table, is always the entire table without exception. The non-clustered indexes aren't
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply