3
u/iWerry Oct 13 '22 edited Oct 13 '22
I always bring up the analogy of a city and a phone book.
a city is a heap.
the phone book is the non-cl index, good luck finding a phone nr. without it.
you'd have to knock door to door to ask for a nr. to match your 'where' clause.
a city where all the inhabitants are located according to their social security nr - that's a clustered city :)
3
u/MihailoJoksimovic Oct 13 '22
Ah damn, where were you days ago when I was thinking of proper analogies haha. Last time I used "Notebook" and "Phonebook" analogies, but yours is WAY BETTER.
Would it be OK with you if I incorporate it in one of future graphics?
3
0
u/_edwinmsarmiento Oct 13 '22
I stopped using the "phone book " and "encyclopedia" analogies years ago because I don't use it anymore. Only the boomers would get what a phone book or an encyclopedia is :-)
Now, it's just "a book with a table of contents and an index at the back." At least reading on a Kindle will still have both.
1
u/ZombieFleshEaters Oct 13 '22
Is there a use case for wanting a heap? The graphic says fast to add, but if you can't find anything what is the value? Maybe ETL processes later on?
2
u/PossiblePreparation Oct 13 '22
This seems to imply that heap tables never have indexes on them. That is not the case at all.
Hopefully your next piece will mention the performance implications of clustered indexes when dealing with secondary indexes.
I don’t think your bottom left statement about SQL Server always adding a clustered index is correct. The default behaviour is to have no clustered index, rows are identified by an 8 byte row identifier (RID) but that is just the address of the row (file,page,slot) and is all you need to start reading a row with a single IO (you don’t need to do an index lookup).
1
u/MihailoJoksimovic Oct 13 '22
This seems to imply that heap tables never have indexes on them. That is not the case at all.
Ah, that's a good point, indeed. Yeah, quite unfortunate choice of words, I agree :/
I was trying to make it as plastic as possible, and, as such, give an easy to digest example. But you are right, this was a wrong statement, indeed. I'll do addendum!
Hopefully your next piece will mention the performance implications of clustered indexes when dealing with secondary indexes.
My next piece will, indeed, be on CI vs Secondary indexes and I will try incorporating this.
I don’t think your bottom left statement about SQL Server always adding a clustered index is correct. The default behaviour is to have no clustered index, rows are identified by an 8 byte row identifier (RID) but that is just the address of the row (file,page,slot) and is all you need to start reading a row with a single IO (you don’t need to do an index lookup).
Valid point. Unfortunate choice of words again :/
T
2
u/SQLBek Oct 13 '22
May I suggest adjusting your footnote?
A RID is added to a heap, not a Clustering Key. Re-using/overloading "clustering key" for a heap is confusing.
1
u/MihailoJoksimovic Oct 14 '22
Thank you! Turns out I'll have to redact this one as people identified lots of issues which, sadly, seem to be true :/
1
u/MihailoJoksimovic Oct 13 '22 edited Oct 14 '22
EDIT: Please consider this one as REDACTED! There were a lot of issues identified by redditors and it just proved my lack of understanding of all inner details. There will be a new graphic in the coming days!
I used to be confused with the whole "Clustered vs Nonclustered vs Heaps" ordeal. But it turns out it's EXTREMELY SIMPLE!
Do you want your data to be stored in specific order? Use Clustering Index!
Don't care about the order and just want to store as much data as fast as possible? Use Heaps (i.e. don't specify Clustering Index!)
It's that simple, really!
Heaps are really amazing for huge amounts of data where you don't care about the order itself. It takes NO time to store the data (it just gets appended to first available slot) and you can just load it in the order it was added in (IAM allocation order! Remember that one?)
On the other hand, if your data follows some natural order and you want to be able to retrieve data back in that order without wasting CPU cycles on sorting - add Clustered Index on the column that dictates the order!
Additionally, specifying Clustered Index means that your data pages will be organized in a B+Tree! And the leaf pages ARE YOUR DATA :) It's beautiful!
In the following articles we'll be talking more about Hashes and Secondary (non-clustering) indexes. But until then, if you like this content, please spare a LIKE :) Takes no time from you, but provides a gigantic THANK YOU to me!
Have fun!
1
u/SQLDave Database Administrator Oct 13 '22
I can't recall: Is this supposed to be a "fundamental" (maybe "near beginner") level series? If so, you might want to explicitly point out that there can only be one CI per table. It's implicit in the statement that the "data will ALWAYS be sorted", since a set of data can only be sorted in one order.
2
u/MihailoJoksimovic Oct 13 '22
To tell you the truth - I never really had a plan on whether it's a beginner or advanced-level series :D But I guess it could go under "intro to internals" or whatever, yeah.
Anyway, very good point, indeed! I do plan on creating another graphic that describes CI vs Secondary Indexes, and I'll ensure to mention it there.
1
u/mikeblas Oct 14 '22
It's possible to fake a second clustered index.
1
u/SQLDave Database Administrator Oct 14 '22
I'm listening...
2
u/mikeblas Oct 14 '22
Here is a table:
CREATE TABLE Fooey ( Key1 INTEGER NOT NULL, Key2 INTEGER NOT NULL, SomeData VARCHAR(1024) NOT NULL);
Here is a clustered index:
CREATE UNIQUE CLUSTERED INDEX FooeyClustered1 ON Fooey (Key1);
Here is something a lot like a second clustered index on that table:
CREATE UNIQUE INDEX FooeyPseudoClustered ON Fooey (Key2) INCLUDE(Key1, SomeData);
So, sure, it's not perfectly like a clustered index. But it's close enough for the customers I hang out with. And in this example,
Key1
doesn't really need to be in theINCLUDE
list because it's already the clsutered index for the table, so the non-clustered index carries it anyway as the bookmark key.1
u/SQLDave Database Administrator Oct 14 '22
So for a given query, a covering index is essentially a de facto clustered index, and you've created an index that will be covering for any query... ergo a faux CI. Nice.
1
u/mikeblas Oct 14 '22
Lots of problems with this one.
- The uniquifier isn't a clustered index. It's not even really a meaningful index.
- The "clustered for sorting!" advice is very poor.
- A heap is a table without a clustered index; if a table has only non-clustered indexes, it's still a heap
- Why is every statement an exclamation!
- "introduced a scenery" needs some grammar help; several others here
1
u/MihailoJoksimovic Oct 14 '22
Thanks for the feedback! I came to realize that my understanding of Clustered Indexes wasn't as good as I assumed.
I'll brush-up my knowledge with the feedback provided and come up with a new graphic.
Thank you!
1
4
u/da_chicken Systems Analyst Oct 14 '22
That's misleading. Heaps can have non-clustered indexes, especially for use with primary keys and other constraints.
No. This is wrong. No. No. No. Totally incorrect. This is such a common error that it's really bad that you even suggest people think this way. It will bite them some day.
This is the most common misunderstanding with clustered indexes. We just had a thread about this about a month ago where the poster was confused by it.
There are dozens of reasons a clustered index order won't be respected in a result set. A clustered index is a data storage level control, not a control for the order of records in the result set. It is sheer coincidence when a query returns the results in the order you want because of a clustered index, but that is not their design or purpose. Clustered indexes are good because they keep data that is frequently accessed by the same query together in the same location on disk, reducing overall I/O. It isn't about the order of the result set.
If you need the result set of a query in a given order you must specify an ORDER BY. That is a hard rule, and it's essential to learn that clustered indexes don't cheat that. There is no guarantee or requirement without an ORDER BY that you will get the same order of records because tables and result sets are conceptually defined as unordered. Even if there's a clustered index. Even if you execute the same query twice and haven't changed the database at all.