we were looking for development DBA.
Honestly, I was in shock from some candidates.
We got many CVs where people wrote that
they have 5-6 and more years of 'hands on' experience.
Nice, I said,
there will be no
problem to find
a good DBA.
BUT,
I was wrong !!!!!!!!
People calling themselves a DBA with many years
of experience did not answer very primitive
questions related to the SQL Server.
Here some examples:
Question:
What is the difference between @@identity and scope_identity()?
Answers we got:
- @@identity returns the process id of your connection.
- @@identity is a constant and you can use it later in the code.
- @@identity is a global variable and scope_identity() is a func.
- "I don`t know, but I can search for it in the Google" (cool answer)
Question:What is the difference between varchar and nvarchar?
Answers we got:
- varchar can hold only letters and nvarchar can hold letters+numbers
- varchar is a fixed set of characters per column and nvarchar is
'flexible' to use, as much characters as needed per row.
Question:
How SQL Server stores its indexes?
What B stands for in the 'B-Tree'?
Answers:
- B is for 'Group by' clause and A is for Child (WTF !!??)
- Binary (90 % of candidates answered Binary)
Question:
How to fix index fragmentation?
Answer:
You update the statistics.
-----------------------------------------------------------
I interviewed a DBA with 10 years of expirence and he:
Did not know what the difference between Identiy and ScopeIdentiy is.
Did not know that varchar(max) hold data in row until 8k.
Did not know what the OUTPUT clause in sql 2005 is.
------------------------------------------------------------
Come on , people !
How dare you lie in your CV and come to job interview when
you know you will be tested.
I really do not get it.
I want to emphasize, these are not the only questions
that were asked. This post is not about which questions
to ask or how to do the interview.
It is just an example of answers.
Are you serious? Do you know everything - all the ins and outs - about SQL? How do you spend your days? On what are you focused? Chances are you know those areas very well.
ReplyDeleteNow I have had many interviews where I wanted to mock the candidate for all of the acronyms they fit into one line in a resume. However, my guess is your HR dept did an equally poor job writing the job description.
I have been frustrated by the HR edits to my position descriptions at least as often because I have to sort through applicants who were misled by the posting by HR.
I would expect a DBA to administer, and a SQL developer to develop.
Since your stated interest is in a development DBA I think the OUTPUT clause, identity and nvarchar questions are quite relevant. I would think that there would be more questions on indexing, SSIS, nested loops, and error handling before any B-Tree questions.
ReplyDeleteAre you referring to rows a tuples too?
Well, no one should expect any one to know everything in SQL Server. SQL Server is a huge product and has many features and functionalities. Database Professionals can be divided into Developer, Administrator, Architect, and BI professionas. You cannot know everything in these areas. For instance, good number of years of experience in administration section but everyday i run into situations where i have never heard of, today i had a problem with one of the production database and got the following error:
ReplyDeleteSQL SERVER – FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
My vision is different, no one can memorize everything, for instance, how can you deal with slow running queries, or how can you write highly optmized queries? These are million dollars questions and no one can have concrete answers. I have seen the job desciption too vague as well. It really depends on what you are looking for. As long as candidate has good understanding of the product, eager to learn, and understands the businss process quickly, that should not be a problem. It is just my thought!!
What do you think the B in b-tree stands for?
ReplyDeleteAnd why would a development DBA need to know that?
Also, your assertion that varchar(max) holds data in row until 8K is incorrect. Total row size isn't even 8 K.
you are correct Robert.
DeleteStanding with Dan for this kind of experience, now a days I'm seeing people who quote 5.7 / 3.9 yrs of emp sigh.. they are into fractions. What Dan is trying to quote is these people may be go through 24 hrs course or sit next to a DBA or so called & note steps to do things on a paper. I've seen 12+ yrs ppl who call them a DBA laugh at Mount point size or people who are confused between AD & SQL accounts. Some one who was talking my interview was admant that a Non Clustered index cant be created with out a clustered index in place. Such biskits are every where.
ReplyDeleteRobert, good points. I am not saying there are people who claim that they have n number of years of experience and that can be found out from the emplyment history, if you like the candidate and think that he/she is capable of doing what you are looking for, i do not think not knowing simple DB terms would really matter. For me, i take book(books) of good writers such as Robert, Kalen, Buck, Brent, Ben Gan, etc and look at the topic/sub-topic in the interview, if i do not remember or know what they asked. I did this few times and had no issue with the interviewing folks. It all depends what you are looking for and your perspective on the candidates.
ReplyDeleteAs I understand it about B-trees, I'm going to take it that this was a trick question. From what I know, there is no definitive idea about the true origin of the "B" in B-tree.
ReplyDelete"B" stands for 'balanced'.
ReplyDeleteA B-Tree keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic amortized time.
A B-tree is kept BALANCED by requiring that all leaf nodes are at the same depth.
According to Wiki
ReplyDeletedefinition -
A B-tree is a method of placing and locating files (called records or keys) in a database. (The meaning of the letter B has not been explicitly defined.) The B-tree algorithm minimizes the number of times a medium must be accessed to locate a desired record, thereby speeding up the process.
however
In computer science, a B+ tree or B plus tree is a type of tree which represents sorted data in a way that allows for efficient insertion, retrieval and removal of records, each of which is identified by a key. It is a dynamic, multilevel index, with maximum and minimum bounds on the number of keys in each index segment (usually called a "block" or "node").
Dan, you should read the history of the B-Tree. Your understanding of it is not correct.
ReplyDeleteAlso, you're confusing a B+-tree and a B-tree. SQL Server uses B+-trees even though it often refers to them as B-trees in documentation.
If I went to your interview I would know immediately that you are looking for some one with certification and not for some one with work experience. It sounds like when you find some one you actually like you will have to teach that person EVRYTHING about doing the job.
ReplyDeleteI am in the business 10 + years.
A word of advice:
If you are looking for a DBA to for a specific roll ask questions pertaining to required duties.
Guys,I don`t care about a certification.
ReplyDeleteI just expect from DBA with 5-6 years
experience to know what @@Identity is
and how SQL Server store its indexes.
And I do not concentrate on the B-Tree
questions,I ask DBAs, how SQL Server stores its indexes and if they answer B-Tree, I want
to see if they know what they are talking about.
Is that too much to ask?
Note to Robert L Davis.
ReplyDeleteWhy do you think the page split
occurs as below:
About half of the data is left on the old page, and the other half is put on the new page.
This is one of the key benefit of the Balanced
trees, they need to be stay balanced.
So, page splits are tightly related to
the structure of B-tree.
For more info, you can refer to:
http://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/
It really all depends on the breadth of experience and exposure a DBA has had - SQL Server is such a massive and broad product, and there's so many ways to use it well (and to use it badly).
ReplyDeleteKnowing what @@IDENTITY is different from knowing how it compares to scope_identity(). Every day, I look at my development tasks hoping that there'll be some application for scope_identity() and it has simply not come up in the last 15 years (I think it's new this millennium, bit I'm open to being wrong about that.) So, you're asking for the information you want in kind of an obstructive way.
ReplyDeleteTask based questions are really all the rage with interviewers these days, and you can kind of see why.
ion
ReplyDeleteIf you know how work both @IDENTITY or SCOPE_IDENTITY() what problem to compare them? You think if it did not come to for 15 years you do not have to know that? IIRW SCOPE_IDENTITY()were introduced in SQL Server 2000, so it is not supprise you have not had 15 years ago
Yep, I'd agree - I've never found a use for or seen anyone else use scope_identity in 14 years as a DBA, both development and production. Equally, I could ask someone what 9.00.xxxx number relates to what hotfix, or what trace flag is used to turn on undocumented DBCC command features.
ReplyDeleteSome things are useful to know on a day to day basis (the index question is s good one), others are fairly pointless and are academic tests of obscure knowledge retention. I'd argue that whilst the 'B-tree' question is of passing interest from a theoretical understanding point of view, it's rare that a conversation about B-trees will enter daily DBA conversation. Far better to know something practical, like how to diagnose that an index structure is badly fragmented and what to do about it, IMHO. But then, despite my background, I take a pragmatic view of my job, which is to provide a service to the business which employs me, not to fill my head with irrelevant academic facts. If the theory backs up my practice, and that makes me a better practitioner, that's fine, but theory is a means to an end, not the end in itself.
My experience of being at both sides of the interview table is that I like to ask both specific technical questions and open questions to see how someone handles them.
Unfortunately, with specific questions, there are invariably specific correct answers, and some unscrupulous recruiters will ask the previous candidate what questions they were asked in order to pass them on to the next candidate - who may be clueless and immoral.
Asking open questions about how to troubleshoot a specific situation is a much better indicator, as the candidate will have to draw more on experience, and may even (positively) surprise the interviewer by their answers. It's a bit more difficult (though not impossibe) to get the answers from a book for these.
But bottom line is, anyone who thinks they know everything about SQL Server is kidding themselves, and probably has been since 6.5.
I like to see candidates get 17-18 questions out of 20 right, 85-90%. Any more than that and they're either a) Amazing or b) clueless but have been told what they'll be asked.
However, I do agree that probably 80-90% of people put forward for DBA jobs are indeed a) inexperienced and b) clueless, often developers who want a pay rise. If you find a good one, hire them, they're rare gems! So whilst I disagree with your methodology, the reasons for it are absolutely spot on ;-)
Jon.
Actually I rarely comment on these things, but after similar experience being interviewed and as interviewer as well, just kind of need to.
ReplyDeleteAfter using SQL Server for 12 years (SQL Developer, DBA), I have to admit I never use scope_identity. Maybe I just use 20 % of SQL Server, like human uses only 10% of brain.
About B-Tree, I never knew B stands for balanced. How could I pass my Computer Science degree last time ? I could not even answer this easy question.
Maybe I have been busy working rather than memorising all the textbooks again.
You have to picture yourself in reverse.
SQL Server is a massive product, if someone know it completely, probably it is better to work for Microsoft rather than your company.
A piece of advice, this is I learned from my boss. Just try him for 1 month (contract).
Sometimes DBA is usually hand-on person rather than textbook guy.
HR
You must be one of those insecure over 40 who thinks he knows everything. Just because a candiate might miss a couple of your questions doesn't mean he couldn't run circles around you as a DBA.....keep that in mind...
ReplyDeleteI don't get suprised most people answer B-tree as binary tree. Even SQL Server MVP ever answers that.
ReplyDeleteDon't trust any credentials/certifications or even "highly exclusive" credentials like SQL MVP whatsoever before conducting one to one interview.
It is nice to see all your comments.
ReplyDeleteIt is very interesting to see that
this subject gets people to write
a comment.
There is additional discussion in LinkedIn
http://www.linkedin.com/groupItem?view=&gid=54395&type=member&item=34880743&qid=b6476ef6-214a-4a5e-aea8-0b80e2e7a26f&goback=.gmp_54395
Let me know what you think.
Thanks again.
I think you are exaggerating.
ReplyDeleteQuestions that you asked are seems not important at all for DBA.
I've been working as a DBA in Silicon Valley since 1996 (SQL 4.2) with 3 different companies - Manufacturing with 300 users, IT software Development company with 30 extremely smart developers and last 8 years with large insurance company. So, my experience covers many different areas.
I'm absolutely not bragging but many people in the company think that I can walk on the water and extremely happy with my performance.
I do not know what SCOPE_IDENTITY() is. We never used this function. Never. I'll tell you more, I remember that I've read couple of weeks ago about it somewhere but probably it was not that important to me , so I do not remember about it. I forgot long ago what B stands for either. The same way I forgot what RX stands for in my Lexus.
Those questions does not worth much. It just took me 2 minutes to answer those 2 questions using Google. 2 minutes. I'm pretty sure if you gave those candidates computer they would have given you correct answer. They would use internet at their job anyway. If they could not find the answer using whatever they want that's a problem.
Your assumption that "good DBA" must know those things is wrong.
Seems to me, those questions more appropriate to developers. SCOPE_IDENTITY() has no value for DBA. DBA will do backups, restore, performance, monitoring databases, space management, updating and creating test environments, reporting, logins, deal with users, developers, management, resolve hardware issues, resolve performance issues, planning and implementing new hardware and many many other things beside writing sql code.
Of course, DBA has to be smart. No doubt about it. But you might miss some good DBAs with those questions. I've hired few people myself. The best question in my opinion is "Tell me what do you do everyday at work?" and then - talk deeper about specific task they perform. If you see smart person - hire. He does not need to be an expert, he will become it in a few month. With so many resources available: Search engines, forums, news groups just ask question people will reply. That's how I did.
I'll chime in about the @@identity vs. Scope_Identity() question. I think it is important to know the difference between the 2 because they DO behave differently. @@identity returns the last identity value created by the session (this could be the wrong term), but essentially when using @@identity if you have a trigger that does an insert on another table with an identity column you get THAT identity value from @@identity. SCOPE_IDENTITY() returns the identity value for the scope of your statement so it is NOT affected by triggers. In my opinion every instance of @@Identity should have been replaced with SCOPE_IDENTITY() when you moved to SQL Server 2000 and now with the OUTPUT clause in 2005+ you should use that.
ReplyDeleteJust my $0.02 on that.
Oh and on SQL Server storing indexes as B-Trees or as B+-Trees. I honestly don't think it is that important in the day to day working. It's much more important to know when an index should be created and on what column(s) than to know how SQL Server stored it.
I feel your pain with the interview process as I was looking for a contractor and couldn't find one.
ReplyDeleteIn my opinion you should be looking for someone who can communicate and learn and solve problems.
You only know/retain what you have been exposed to lately. I can run circles in the job you descibe but I wouldn't have those answers on the top of my head.
But I could google them and learn them.
the answers I find will have no relavance to my performance getting done the tasks you have.
ps. I know several people who get the tests, cram for them, then pass the test. The certification process is ridiculous. I haven't taken one since working on the Sybase 10 test.
Hi everyone,
ReplyDeleteWe all know that some people lie in their resumes. Personally, I don't care about acronyms or certfications. When looking for IT people, mainly developers or DB, first thing I look at is their technical background. I believe that BSc in Computer Science or related fields is very improtant because that's the foundation. If they come from reputable universities, I don't even need to waste my time asking questions about B-Trees, they will most likely know what balanced trees are and also we all learn how to implement them (inserting nodes and re-organizing the tree programaticaly, etc. I could probably want to waste more of my time and ask them about lexical analisys or compiler design. Anyways .. the main thing here .. when hiring someone is to look for strong and solid background + situational questions (what steps would you take in order to figure out what the resopn is for bottlenecks, long-processing queries, etc. Questions like @@IDENTITY or SCOPE_IDENTITY could be used but I would not pay too much attention to that (for me it would be much more important to ask questions like: "imagine a developer comes to you asking about the possibility to write a SQL Statement and return the value of the identity field (autoincrement) in one single ad-hoc shot ? .. is it possible ? if so .. how would you do that ? .. Hint: the answer has to do with scope_identity() function) ... therefore we need thinkers, people who can think analytically ... etc.
Anyways .. those are my two cents.
Marc Bueno
Trainer and Consultant
Software Training Academy
http://www.SoftwareTrainingAcademy.com
I don't think your questions were out of line... I also don't think everyone will know everything, but sometimes you get what you need by seeing how they deal with what they obviously don't know. We always tried to have at least one 'deflating' question within the technical interview.
ReplyDeleteThat being said:
Anyone who believes (or guesses) that nvarchar is for numbers and varchar is for characters is wasting your time and theirs. The respectable answer is 'I don't know.' but not everyone can get those words out when they should.
I've spent a few years as a 'DBA' (admin) and a few more as a 'DBA' (developer). I have two degrees neither of which are in CS... I didn't know what the B stood for either, but I'd have told you that. (and googled it afterward)