This month we are going to look at a topic near and dear to both database developers and database administrators alike. Indexes.

Even if you don’t actually do everything on this list right now (and it’s a lot) make sure you at least understand it all and are capable of doing all of it when you need to.

• Clustered Indexes (CI)
  • Create a new table with a CI at the same time.
  • Remove the CI from the table.
  • Modify the existing table to add the CI back.
  • What column(s) did you select? Do you know why? Was it rote or did you have a reason?
• Non-Clustered Indexes (NCI)
  • Create a new table with a CI and an NCI at the same time.
  • Remove the NCI from the table.
  • Modify the existing table to add the NCI back.
  • Same as above. What column(s) did you select? Why? Did you have an actual reason or did you just pick something randomly?
• Maintenance
  • Rebuild and Reorg
    • a single index
    • all of the indexes for a table
    • all of the indexes for a database
  • When should you reorg vs rebuild?
  • How do you tell?

I understand there are large chunks of indexing that I’m completely skipping. This however this aimed at junior and mid level DBA/database developers, and while the rest of it is certainly important, I would consider it (XML, Columnstore, etc.) more advanced and less commonly used.

On top of that I understand that for someone more advanced this is pretty easy stuff. However, can you do all of it off the top of your head? If you can, great! If not, well, some practice couldn’t hurt right? I will (and always do) be going through this stuff myself just for the practice.

I also didn’t specify GUI or script. You can use the GUI if you really want to, but I’ll be honest it’s going to be SO much harder I didn’t really consider it a high probability.

Last but not least you might notice no grades again. That’s because honestly, I couldn’t think of how to break this up. I realize for a developer, index maintenance may not seem all that exciting, and vise versa for someone who’s strictly an administrator. My personal opinion is that both are wrong and all of this is equally important. So give it all a shot and you can consider this a pass/fail homework.