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.
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, etc...
So currently, I have the clustered index on the primary key. This is one table that is accessed 99% of the time by
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
OpponentId. If the
EventType is 'Tournament', Then uniqueness is on
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
WrestlerRanking table ----------------------------- WrestlerRankingId int primary key clustered, Season int not null, Week int not null, IsCurrent bit not null, WrestlerId int not null, etc...
In this scenario, similar to the one above, 99% of the this table is accessed by
IsCurrent. Uniqueness can be set by
Would/should the clustered index for this table be on
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).