I am not really sure how to title this question.
Last week I went on an interview for a level II DBA position. I have been doing basic administration for database servers for several years. My primary role has been to make sure that servers are patched, that SQL jobs complete successfully, create new reports, ensure backups are valid and can be restored, run vendor supplied scripts for upgrades, make sure we have plenty of drive space available, etc. I didn’t really know what to expect on this job interview other than to talk about what my current role is. What I did experience was more of an oral exam. Some questions I knew but others I was stumped by. I guess they started out easy on me such as name the system databases, name three recovery models, etc. The question that really got me was about indexes.
I was asked to explain the difference between a clustered and non clustered index and how many of each can you have. I didn’t know how to respond. I was trying to use inductive reasoning to come up with something but felt it best to just say, I don’t know. I my past several years of working with SQL Server I haven’t had to do anything with indexes other than make sure our optimization job that does something with indexes runs successfully.
So I guess my question is twofold. One, can someone help explain their question to me, and second, is it customary for job interviews for DBA positions to be such that one feels they are being given an oral exam?
asked May 31 '11 at 12:15 PM in Default
@Mikelanders, I will answer your second question first. YES, it is very common. In fact many companies also give written exams as well. It would benefit you greatly to start attending online webinars as well as joining a SQL Pass Chapter. If there isn’t a SQL PASS Chapter close to you there are dozens of virtual ones where you can attend online. If you aren’t a member of PASS I suggest joining so you can get emails on upcoming events. Hey, its FREE so why not. SQL PASS There are also tons of books out there you should pick up. Thomas LaRock has a great book DBA Survivor . Brad McGehee also has a great blog and resources on DBA Best Practices. Many questions folks ask are about best practices with databases. It would help you a lot to know them, not only for an interview, but for your career as a DBA.
To answer your first question. A clustered index is the logical order of the data in the table. Since the cluster index stores the actual data in the table there can only be a one clustered index per table. Think of the white pages of a phone book. That data is sorted ASC by last name and each page is numbered. For Non-Clustered indexes think of an index at the back of a text book. The index is stored in the back of the book but points to the data elsewhere in the book. Typically the clustered index however if no clustered index exists then it points to a row identifier and the data may not be in any logical order. A Row ID (RID) is comprised of the file number, page number, and slot number of the row. Also you should know that a table without a clustered index is referred to as HEAP table. As for the number of non clustered indexes it depends on the version of SQL Server. In my opinion neither of these two limits should ever be hit but for educational purposes one should know them. SQL 2000/2005 = 249, SQL 2008 = 999. I should also mention that there are other indexes you need to be familiar with as well. You can have covering indexes starting with SQL 2005. Basically these are Indexes with Included Columns. Think of them as composite indexes without taking up as much space. If you want a great set of articles to begin reading to help understand the fundamentals of indexes I would suggest reading this three part series by Gail Shaw. Introduction to Indexes
To answer your question directly: Here is what Clustered and Nonclustered indexes mean. There are many articles out there on the web(Link1, Link2, Link3) and also many books and read as much as you can. But many say its an art as much as it is a science; so it might take you a while before you master it. Understanding indexes is a key DBA skill used to optimize database performance and if you master it will give you more performance boost easily than any other technique.
As for the interview it depends, different interviewrs have different styles and may as a result use different kind of approaches. So you may be asked textbook like questions from time to time while others may ask you your perspective on different issues expecting you to explain from your experience.
answered May 31 '11 at 12:41 PM