I recently saw the worst example of how not to design a table and indexes in SQL Server.
So here it is:
Role : Log any activity that a user is invited for.
PrimaryKey: … Wait for it … tada : UserID,ActivityID,ActivityType,AddDate … Wait you say …. YES … all table columns are used for the primary key and of course by default this will be a clustered index. Which means that if you want to insert 100K records in this table … it will split a lot of pages to reorganize the index beside actually adding the table records.
Wait … it gets better…
Someone created a non clustered index on ActivityID,UserID,,ActivityType,AddDate … yes in that order … and NO it does not help but I guess not everybody knows that a non clustered index will contain the clustered index besides the actual index.
So let’s recap:
Table : 4 Columns
PK Clustered Index: Same 4 Columns
Non clustered index: Same 4 columns x2
Insert efficiency -> extremely poor.