I have a system that has been up-and-running for almost 3 years now. Previously (and currently) the front-end was using NHibernate for all of the database access.

Right now, I'm in the process of converting over to Dapper and utilizing stored procedures for EVERYTHING (reads, writes, etc).

Now that NHibernate is gone, I don't believe the current structure of tablename, tablenameid (clustered primary key) is optimal anymore for every table. Lots of my tables will NEVER be accessed via the primary key, UNLESS it's for a delete (even that can be avoided).

So, here are a couple examples of table structures, and which fields I believe should be the clustered index.

Example 1:

Event table
EventId int primary key clustered,
Season int not null,
EventDate datetime not null,
EventName varchar(100) not null,
EventType varchar(10) not null,-- (soon to be it's own table, but not right now...there are only 2 possible values, Tournament or Dual)
SchoolId int not null,
OpponentId int NULL,

So currently, I have the clustered index on the primary key. This is one table that is accessed 99% of the time by Season, and SchoolId. (Very rare that the EventId is used, when it is, it's for a delete)

Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be null).

Under this architecture, I don't believe I can/should have a unique key, which isn't really my issue...

Am I safe to assume that the clustered index for this table should be on the Season and SchoolId columns?

Example 2:

WrestlerRanking table
WrestlerRankingId int primary key clustered,
Season int not null,
Week int not null,
IsCurrent bit not null,
WrestlerId int not null,

In this scenario, similar to the one above, 99% of the this table is accessed by Season, WrestlerId, and IsCurrent. Uniqueness can be set by Season, Week, and WrestlerId.

Would/should the clustered index for this table be on Season and WrestlerId, even though the majority of joins to this table will include the IsCurrent column? This is definitely not unique as there could be around 20 records for the Season and WrestlerId combination (20 different weeks of rankings).


Here's where the trickiness comes in. If the EventType is 'Dual', then uniqueness >is by Season, EventDate, SchoolId, OpponentId. If the EventType is 'Tournament', >Then uniqueness is on Season, EventDate, SchoolId, EventName (OpponentId will be >null).

I'd look at what's common between these two queries to determine what columns to use in the Clustered Index. Remember, the Clustered index is effectively how the data is arranged on the disk, so it should have a structure similar to the hierarchy that queries against the table will reference. If every query, or the most critical ones, all look at Season, SchoolId and EventDate regardless of EventType, then I'd use those in the Clustered index. Additional Non-clustered covering indexes can be created for specific queries (around EventType or OpponentId, for example) which INCLUDE the columns returned to prevent key lookups.

Clustered indexes do not need to be unique, they need to specify how the data should be arranged so that queries can quickly seek to the desired location. The primary key should be unique, but it doesn't have to be a clustered index. If it's just a reference for a row value that doesn't have any useful information that queries will use (i.e. you never join on it), then it should not be part of the clustered index.

For the second example, I would probably use Season, Week, and WrestlerId if those are most queried. The order should be in the logical heirarchy that the data is queried in...which brings me to my next point:

All this advice is contingent on the execution plans of your queries.

Always, ALWAYS, use actual execution information to determine what the best indexes for your queries are. Got some table scan issues? Implement a clustered index. Got a Clustered index scan slowing you down? Look into implementing a non-clustered covering index for that query based on the columns it's looking up (often suggested by the DB engine itself). Got key lookup? Try using an INCLUDES on the affected index with the looked-up columns. Indexing hypothetically, before you're super familiar with the DB engine, often leads to wasted time and rework. I'm not saying to shut your brain off and just blindly follow the query plan's suggested indexes, but instead use the query plan to build your indexing strategy rather than trying to preempt it, or second guess it.

  • For the second example, you suggested Season, Week and WrestlerId. Does it matter how the joins are constructed? Here's how EVERY join would work... join WrestlerRanking wr on wr.WrestlerId = x.WrestlerId and wr.Season = @season and wr.IsCurrent = 1. So would it make sense to create the clustered index in that same order, WrestlerId, then Season? Also, should I really include Week (or IsCurrent)? – ganders Jul 29 '16 at 17:54
  • You want to go from the largest grain to the smallest. Think of it like the DB engine would. If you put wrestlerid first, then it's basically going to seek through the entire table because the id is arbitrary in relation to season. If you put season first though, it'll immediately discount every row in the table that doesn't have a Season = @Season BEFORE it starts joining on WrestlerId. IsCurrent should proabably also be in the clustered index if its in every join. Whatever fields you need to return that aren't part of a join are good to put in the INCLUDES of a covering Non-clustered index. – Haris Khan Jul 29 '16 at 18:10
  • Ah, now I get it, thanks for the explanation!!! – ganders Jul 29 '16 at 18:32
  • No problem! As a rule of thumb, you always want to get the table down to the smallest possible subset of relevant data before joining the rows together. This is why table partitioning can be so useful on larger tables. Think about that both when you're writing queries and when your designing indexes and tables. :) – Haris Khan Jul 29 '16 at 18:36

Have you tried to run an execution plan on the series of queries/statements that will be running against the table? The execution plan (especially in the later versions) is pretty good at suggesting indexes. If you have and it is not suggesting any, can you post the execution plans for review?

is there a particular reason you need it to be clustered? Obviously it will perform better, but I would try non-clustered first to see if it gets you the desired performance.

  • 1
    "is there a particular reason you need it to be clustered?" - I was going through my "Index Usage Statistics" query/report to see how everything was performing, and these 2 tables (along with a few others) had very bad/low index seeks, and LOTS of index scans on the clustered indexes. – ganders Jul 29 '16 at 15:46
  • 2
    There are a few handy utilities for this exact situation, check out Brent Ozar's site and sp_BlitzIndex. It's great, something we use regularly for index maintenance of new and existing. – Jason B. Jul 29 '16 at 16:01
  • 1
    @JasonB. ha, I just ran BlitzIndex and got this No Major Problems Found: Nice Work – ganders Jul 29 '16 at 17:48

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.