0

I am new to SQL, and I have been reading different articles about indexes.

Clustered index vs non - clustered on heap table

[Non-Clustered on clustered index vs non-clustered on heap table] (Performance of Non Clustered Indexes on Heaps vs Clustered Indexes)

So, it seems like the winner in the first case is clustered index while in the second case the heap version is better.

My question is the following

Will choosing a good cluster index key and good non-cluster index keys guarantee the best overall performance ?

Hovhannes Vardanyan
  • 193
  • 10
  • You might be interested in this: https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key –  Aug 21 '18 at 12:32
  • 1
    Please don't try to learn the billions of "always do X to guarantee best performance rules". More often than not they're wrong or at least come with significant nuance. Keep things simple. Set performance goals. Write. Measure. Where you're not meeting your goals, focus your attention there. And the answers you get that time may be completely different from the next time you're writing something (even when it's quite similar) – Damien_The_Unbeliever Aug 21 '18 at 12:41
  • Thanks for the article @a – Hovhannes Vardanyan Aug 21 '18 at 12:58
  • @a_horse_with_no_name, but after reading this article I sort of came back to the same question – Hovhannes Vardanyan Aug 21 '18 at 12:58
  • Why are people using clustered indexes with non-clustered ones, if they can have all of the indexes non-clustered with heap tables ? – Hovhannes Vardanyan Aug 21 '18 at 12:59
  • 6
    If you're new, then cluster your table on an `IDENTITY`, and then create non-clustered indexes as necessary for your queries. Even where it's not the best possible thing to do, it works well enough that anything else should be considered optimization, and that should not be premature. What you should almost never do is take the numbers from someone else's benchmarks and base your design on *that*. At the very least, if you think they're on to something, do yourself the favor of getting results for your own setup. Whether you succeed or fail, you'll have something interesting. – Jeroen Mostert Aug 21 '18 at 13:01

0 Answers0