Thank you for your article.
I would appreciate some information on how to choose a clustered index and constraints.
I have read articles about hotspots and not choosing a incrementing id and then other articles that say this was meant for 6.5. So please explain 🙂 I really don't have to deal with transactional data even for my data tables so I'm not sure that hotspots are something I need to worry about.
I created a clustered index on the id and set is as my pk and a non clustered unique constraint on several columns that by definition have to be unique. I have read some things by Celko that I believe says incrementing fields are extraneous and he would prefer just using the compound pk. But, I like my id for foreign keys.
I also read that size matters and if it's a small table a full table scan takes place anyway. My look up tables are small but my data tables house millions of records.