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?