2

I'm learning more about indexes in general, and clustered indexes in particular.

In this article by Markus Winand, he makes an excellent case for not using the primary key of a table as the clustering key.

He stresses index-only scans over using clustered indexes, and shows how you can use non-clustered indexes to get really fast results.

Can someone explain why Entity Framework code-first does not provide the means to store a table as a non-clustered-index?

What are the benefits of having every table clustered?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Winks
  • 580
  • 1
  • 7
  • 17
  • How about creating UNIQUE and NOT NULL column and do not use PK at all? Will EF Code First create clustered index then? – Lukasz Szozda Nov 21 '17 at 15:49
  • 2
    I understand you're thrilled about this stellar blog post, but I'm afraid the questions you're asking are too broad for Stack Overflow. For one, it's always a guessing game to answer questions about implementation decisions of third-party libraries. Usually the only possible answer is: ask them. – Gert Arnold Nov 21 '17 at 15:51
  • 1
    @lad2025 that's an interesting idea. See this https://stackoverflow.com/a/20404899/660223 as well. I'm going to try it out and see what happens. – Winks Nov 21 '17 at 15:51
  • 1
    Read everything Kimberly Tripp (the *Queen of Indexing*) has written on the topic: [The Clustered Index Debate - continued](https://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/), [Ever-increasing clustering key – the Clustered Index Debate……….again!](https://www.sqlskills.com/blogs/kimberly/ever-increasing-clustering-key-the-clustered-index-debate-again/); She explains in great detail **why** a well-chosen clustering key makes **all operations** (***YES*** - even insert and delete!) faster in SQL Server – marc_s Nov 21 '17 at 16:39
  • 1
    Once you've read and digested all of Kimberly Tripp's excellent advice, you'll quickly see that putting the clustering index on the primary key *is* in fact a very wise and useful default. Agreed - there are cases when it might not work out - but those are relatively rare. As a **default**, putting the CI on your PK is a *good and sane* choice. – marc_s Nov 21 '17 at 16:42
  • 1
    @marc_s thanks for the resource. It's good to have more perspective and take all factors into the equation. – Winks Nov 21 '17 at 16:53
  • @lad2025 if you don't care about Orphan records and consistency of your db, then yes, you don't need PK – Ruslan Tolkachev Nov 21 '17 at 20:02
  • @RuslanTolkachev You could easily create FK to UNIQUE column. So your argument is invalid. **[Demo](http://rextester.com/KTK24307)** – Lukasz Szozda Nov 22 '17 at 15:02

0 Answers0