SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – April 2018 – Indexes

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineThis 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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...