Interview Bootcamp
Vote

0% completed

​

Types of Indexes

All indexes do the same job: they help the database find rows without reading the whole table. They differ in what they promise, what goes into them, and which queries they can serve.

It helps to hold three groups in mind:

  • What the database promises. Primary and unique indexes.
  • What goes into the index and where it sits. Clustered, non-clustered, composite, covering and partial indexes.
  • A different structure entirely, because a B-tree cannot answer the question. Full-text, hash, bitmap and geospatial indexes.

.....

.....

.....

Like the course? Get enrolled and start learning!
Alexandre Gobeaux

Alexandre Gobeaux

· a year ago

Additionally, non-clustered indexes on a clustered table use the clustered key as a pointer to locate data (instead of a direct physical pointer), which adds a bit of size to those indexes.

Why don't we have a pointer? Is it because the pointer can change when adding/removing rows and we don't want to update the non-clustered index?

However, storing the clustered key means when we search it, it will not be O(1), right? O(1) could be provided by a having pointer (talking only about the complexity of finding the row in the table once we have found the index value in the non-clustered index table).

Please explain this a bit more, it would be great having an example or even images for this specific non-clustered index on a clustered table case.

Show 2 replies
Rohit Bhanot

Rohit Bhanot

· 18 days ago

Its unfortunate that a content of this quality is paid !! Its incomplete, vastly outdated, is missing topics B-Trees, Geospatial indexes. A ton of free resources on internet are far superior than this. While I agree that content should be beginner friendly but that no way means you cut down on quality.

I am actually regretting buying their life time subscription, because its not just one off course or topic issue, the trend is general and spread across almost all of their courses. Content outdated by months if not years, not refreshed, lacks detailed by any means for mid to senior level candidates let alone Staff+ levels.

Some of their courses/chapters are just blobs of text with no intent of engaging the reader, while others use AI generated diagrams which again shows the lack of eff

Show 1 reply