Clustered Index - Trade-off Queries VS Inserts?
30k to 40k rows of Insert/update/Delete is not considered a big problem.

CI should be the most searchable column of each table.

Yes CI do have impact on DML due to index fragmentation.As you are saying there can 30k insert in same showid.You can minimise this be tuning correct Fill Factor.

Choosing correct fill factor will avoid frequent page split.

Correct application design (Not look and feel),DB design and optimize query is very important.

As per you explanation one user insert 30k rows and next minute it will analyse those 30K rows.That means Insert/Select both are equally important in your application and Select have more weight-age than insert.

So CI will be there in each table.only concern is correct choice of KEY and fill factor.


  I mean, it would be slower if it was with different ShowId? My
  understanding is, since this is CI, then each insert would have to
  physically shift positions to insert. Is that true? If so, why having
  the same ShowId makes any difference?


Sorry If i am wrong.See if there are 30k insert,and showid is CI and also there are different Showid then insert will be relatively slower.Why ?
Because CI are ordered and optimizer will first search for  for correct sequence of each page then insert showid one by one.Example Showid 2 will be inserted between 1 &amp; 3 and showid 34 will be inserted between 33 &amp; 35 and so on.So optimizer task will increase.

In case of similar showid,optimizer have to search just once.

I think this link will clear your doubts,

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

Of course first read about fill factor also.

And Rebuild Index every 1-2 days.