SQL Homework – April 2018 – Indexes

Leave a comment

April 2, 2018 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,469 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: