asked
Interviewing for a DBA Position.
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?
@Mikelanders - you're attempting to move on up (and that's great!) but the competition becomes better and you will face these interviews as potential employers try to get a feel for the depth of your knowledge. Sometimes they are a little short-sighted but not uncommon to be sure.
I'd have said that the answer to #2 is "When you want to tease some heated discussion out of a SQL forum."
I'd like to answer the second part of your question in a few days - I want to put the list of SQL Server related questions that I am asking in Dev interviews currently - but I can't right now because I am currently interviewing. If you would like to see them immediately, then drop me an email... :)
Ok, here are the interview questions I currently ask: 1. What is the difference between a clustered and a non-clustered index? 2. When is it best to use cursors? 3. What is the difference between a primary key and a unique constraint? 4. What is a windowing function? 5. What is SARGability? 6. What are the inserted and deleted tables? Where can you use them? 7. What is @@IDENTITY? When should it be used? 8. What would you look at when trying to improve the performance of a SQL Statement? 9. What does MERGE do? 10. What is a collation? 11. What is the difference between UNION and UNION ALL?
@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](
http://www.sqlpass.org) There are also tons of books out there you should pick up. Thomas LaRock has a great book [DBA Survivor](
http://dbasurvivor.com) . [Brad McGehee](
http://www.bradmcgehee.com/) 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](
http://www.sqlservercentral.com/articles/Indexing/68439/)
@TRAD +1 This is a very good answer, but you should probably restate the part about the physical order :) because as stated in Gail Shaw's article you reference:
Clustered indexes define the logical order of the table. The leaf level of the clustered index has the actual data pages of the table. Because of this there can only be one clustered index per table.Gail gives a pretty convincing argument about why the order is logical not physical: if you rip the phone book apart and throw pages on the floor, page 2 is still after page 1 thought these are not ordered but are on the floor in the rather random order.
Good catch. It has been awhile since I read Gails article myself. I will update my answer.
Awesome answer, but at the risk of being nitpicky there is one point to clarify. A covering index includes all of the columns required for a query. It was entirely possible to have covering indexes in SQL Server 2000 and even earlier, but the option to include columns made it much easier to do so in an effecient way.
@TimothyAWiseman Thanks for the clarification. This is why I love SSC so much. When I first learned of covering indexes was shortly after I joined the DBA team. A fellow DBA was explaining covering indexes to another coworker and stated it was new to SQL 2005. I guess that was a hidden gem with SQL 2000.
Might also be worth mentioning that `SELECT foo FROM Table_With_Clustered_Index` doesn't guarantee the data is returned in the index order...
To answer your question directly: Here is what [Clustered][1] and [Nonclustered][2] indexes mean. There are many articles out there on the web([Link1][3], [Link2][4], [Link3][5]) 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. [1]:
http://msdn.microsoft.com/en-us/library/ms190639.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms179325.aspx [3]:
http://www.sqlservercentral.com/articles/Indexing/68439/ [4]:
http://www.mssqltips.com/tip.asp?tip=1206 [5]:
http://www.sqlservercentral.com/articles/Indexing/71726/
1 Person is following this question.
