Say I have a table BlogPost and a table BlogComment. Obviously a BlogPost can have many BlogComments. The most important query of the BlogComment table would look something like this

select * from BlogComment where BlogPostId = @postId

In this case, doesn't it make sense to define the clustered index like this?

create table BlogComment (
    Id bigint identity primary key nonclustered not null
    , BlogPostId bigint not null foreign key references BlogPost(Id)
    , CommentText varchar(max) not null
create clustered index x on BlogComment(BlogPostId, Id);

My reasoning is that when you want to fetch all the comments for a given post, you are likely to only have to pull a few pages from disk into memory. If you were to cluster on the identity column, then every relevant comment might be on its own page.

I think that this clustering strategy means that adding a new comment could cause a page split, but since comments are read far more often than written, this seems like the correct tradeoff.

Is this a good strategy in general whenever you have a parent-child relationship and you know you want to optimize for "fetch all children of a single parent" queries?

  • 1
    Seems fair to me. You don't have much wiggle room in the need of a covering index and your primary key is short which makes it a good candidate for all non clustered indexes. – Ali Razeghi Oct 13 '16 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.