February 28, 2008 at 6:54 am
The guy asked me:
What is an index? --> I answered fine and said there's clustered and non-clustered.
What is the difference? --> I answered fine and said clustered actually sorts the records and non-clustered stores pointers to the records.
He said: Which one is better? ---> umm... I didn't know what to say
He said: If you need to do a lot of SELECT, would you use a clustered or non-clustered index? --->> umm.. I didn't know again!
He said: If you need to do a lot of INSERT, would you use a clustered or non-clustered index? ---> umm.. I don't know again!
How would you answer to the last 3 questions??
You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!
Also, if anyone keeps a list of interview questions, I got burned on "can @@RowCount be set or is it read-only?" and "what is the difference between a primary key and a unique key?"(something about NULLS!)
February 28, 2008 at 7:48 am
Mark Markov (2/28/2008)
He said: Which one is better? ---> umm... I didn't know what to sayHe said: If you need to do a lot of SELECT, would you use a clustered or non-clustered index? --->> umm.. I didn't know again!
He said: If you need to do a lot of INSERT, would you use a clustered or non-clustered index? ---> umm.. I don't know again!
How would you answer to the last 3 questions??
You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!
Also, if anyone keeps a list of interview questions, I got burned on "can @@RowCount be set or is it read-only?" and "what is the difference between a primary key and a unique key?"(something about NULLS!)
Which one is better really depends on what we're talking about. For selecting an individual row, a covering non-clustered index, according to Itzik, is the fastest way to go, period. For selecting sets of data, clustered indexes generally perform faster. They honestly have different functions. The clustered index acts as a storage mechanism, as well as a retrieval mechanism where as the non-clustered index is primarily a retrieval mechanism.
If you had to do lots of inserts, you'd want to test this, but you either want only a clustered index or possibly no index at all. I depends on your data, but the general rule is, a clustered index on every table.
There's not much difference between a primary key & a unique key except that, you can only have one primary key and you can have a number of unique keys and the primary key will not allow nulls where as the unique key, depending on how its structured, does. There's more discussion here too.
Do I get the job or do you want more detail?
One piece of advice, I suspect these are meant to be open-ended questions, questions that elicit, not a yes or no, positive or negative, 42, style of response, but instead spark a discussion as a way of determining how much depth of knowledge you have. You're not likely to get an answer here that you can carry into an interview as a quick way to answer these kinds of questions. My favorite question to weed out the weak is "what's the difference between a clustered and non-clustered index." Once they get past that, I give them the real question, "So, a user calls and says the database is running slow. What do you do?" That's when things get fun in the interview or I say "Thanks for your time, our HR people will be in touch."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 7:54 am
I'm curious, Mark... what position were you applying for?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2008 at 7:58 am
One piece of advice, I suspect these are meant to be open-ended questions,
I suspect they're really meant to find out if you actually know SQL Server or not. 😛 I've had folks that claim to be a "9 out of 10 in SQL server" right on the resume... one of them couldn't tell me how to get the current date and time in a query... the answer was "Dunno, we always used the GUI for that." S-w-e-e-e-e-t... just what I need for an SQL Developer...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2008 at 8:06 am
Yeah, we had a guy who said he was a 10 on internals. We asked him how an index was stored... "In the database."
Ta-Da!
Thanks for playing. Our parting contestants will receive NOTHING! :hehe:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 8:12 am
Dear Grant Fritchey! You have helped a lot with explanation of indexes!!! Now I know what to tell them if I ever get lucky again to get an interview!:D
Could you please please tell me what you would expect to hear in response to your question "The database is running slow. What do you do?" I am not asking that you write a novel here (I know it's a lot of information) but if you can tell me the most important points you would expect to hear I would really appreciate it! I think one way is open SQL Profiler and see who is running what queries but I am not sure... Thanks!
February 28, 2008 at 8:13 am
Jeff Moden (2/28/2008)
One piece of advice, I suspect these are meant to be open-ended questions,
I suspect they're really meant to find out if you actually know SQL Server or not. 😛 I've had folks that claim to be a "9 out of 10 in SQL server" right on the resume... one of them couldn't tell me how to get the current date and time in a query... the answer was "Dunno, we always used the GUI for that." S-w-e-e-e-e-t... just what I need for an SQL Developer...
Even I know! Even I know! It's GetDate() 🙂
February 28, 2008 at 8:20 am
I'm mainly interested in hearing how they're going to troubleshoot stuff. I want to hear what they're going to do, how, when... I've had interviewees that insist the only way to find out what is running slow on the server is to grill the end-users or the developers to find out what they did or what they wrote instead of, oh, I don't know, connecting to the bloody machine and seeing what's happening. Yes, Profiler, then perfmon, sp_who2, Activity Monitor, error logs, wait states, locks... We'll talk. I'll get an idea of knowledge & experience. That's the point. Again, you have to get past the silly trivia contest to get to this question (and other open ended questions, I'll start drawing schema's on the white board and asking you to stub out queries, interviews are fun) like "tell me how many recovery models a database can have and what are the differences between them." I hate those questions, but they're out there to weed out the pretenders.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 8:26 am
Grant is right. One thing that helps is if you visualize your indexes a bit.
The leaf pages of clustered indexes will contain the full row data, the leaf pages of nonclustered indexes
will contain a row locator (either a row ID in case of heaps, or the cluster key)
When you look up an individual row based on an index, you need to start at the root of the index's B-tree, and choose the appropriate child. In case of clustered index you will get to the row data at the end,
in case of a non-clustered index you will get the row locator, so you will need to find the contents of this row later. If it was a clustered table, that means once again traversing an index structure (this time the clustered index). If it was a heap, then it is just lookig up the relevant database page, which is fast.
Every level of an index's B-tree is also doubly linked, so for ranges you can follow these links.
If you imagine these structures it is usually much easier to answer questions like you were asked 🙂
PS: An interview is a two way process: Do ask them questions too 🙂
Regards,
Andras
February 28, 2008 at 8:40 am
Grant Fritchey (2/28/2008)
I'm mainly interested in hearing how they're going to troubleshoot stuff. I want to hear what they're going to do, how, when... I've had interviewees that insist the only way to find out what is running slow on the server is to grill the end-users or the developers to find out what they did or what they wrote instead of, oh, I don't know, connecting to the bloody machine and seeing what's happening. Yes, Profiler, then perfmon, sp_who2, Activity Monitor, error logs, wait states, locks... We'll talk. I'll get an idea of knowledge & experience. That's the point. Again, you have to get past the silly trivia contest to get to this question (and other open ended questions, I'll start drawing schema's on the white board and asking you to stub out queries, interviews are fun) like "tell me how many recovery models a database can have and what are the differences between them." I hate those questions, but they're out there to weed out the pretenders.
Mr. Grant Fritchey,
If I run into you, the interview will be over in 5 minutes and your HR people will get back to me (next lifetime!)
But... I noted down every word you said. The most important thing is that I WANT to learn, right??
He also asked about deadlocks and why they occur, I mumbled something about isolation levels and he seemed "somewhat satisfied". Do you ask this too?
Do you ask design questions such as Ralph Kimball methodology, star, snowflake, etc.? Normalization? I just finished Ralph Kimball's Data Warehouse Toolkit and I was prepared for that type of questions but he did NOT ASK ANY thereby not even opening the door for any such discussion =((
P.S. Simple, Full and Bulk-logged? It's easy when I got Books Online in front of me :D:D
February 28, 2008 at 8:51 am
Oh yeah, we'll ask about deadlocks too. Isolation levels would be a grossly inadequate answer, just so you know.
The thing is, we seldmon hire for entry level dba positions. Much more often we grow those inside the company. So when we're out trying to hire, even for consultants, we need people who really know what they're doing. I mean, we don't expect to get Jeff Moden in every interview (although, that'd be GREAT). When you say you know SQL Server, there is an expectation that means deadlocks, indexes, TSQL syntax, backups ... you kinow, the basics. If you say you know it really well, then I'm going to look at that as a learning opportunity for myself. I'll start presenting you with problems I've run into in recent weeks, solved or not. If you're pitching in, teaching me new tricks, you're hired. If you're looking at me like I just sprouted a weed out of one ear... "Our HR people will be in touch."
I couldn't answer questions intelligently about star schema's, but then I'm not about to claim that I know anything about them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2008 at 9:06 am
When I'm interviewing, I ask questions on any or all of the below topics, depending what position I'm interviewing for.
Indexes - types, differences.
Statistics - what, why
Database corruption - how to find, how to fix
Backups - types
Recovery modes - impact on recovery, backups
Restores - How, what order, common errors
Deadlock - causes, solutions (no, isoaltion levels aren't a cause or a solution)
Optimisation - how, methods
Performance monitoring - what do you look for.
Database theory - assorted questions
T-SQL - assorted (though I leave most of that to the technical test)
Like Grant, I'm not hiring entry level. I'm currently looking for someone to assis me with optimisation and complex problems. When I do interview entry levels, I'm much, much more lenient.
That said, if you say you're an expert on SQL, expect me to ask expert level questions. If you say you're average, I'll be asking average level questions.
As for your initial question, I'd guess he was looking for Select - nonclustered and insert - clustered. It's a massive simplification, but....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 9:06 am
Um... star schema's? Isn't there a sidewalk like that somewhere in Hollywood? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2008 at 9:12 am
Mark Markov (2/28/2008)
You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!
Are you being honest up front about your lack of knowledge?
I'll sometimes recommend someone who knows little, but is intrested in learning. If, however, they've lied on their CV then there's no chance I'll recommend.
Ay skilled interviewer will be able to tell memorised answers from actual knowledge in ust a couple of questions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 28, 2008 at 9:13 am
Jeff Moden (2/28/2008)
Um... star schema's? Isn't there a sidewalk like that somewhere in Hollywood? 😀
Might be. I know I've heard something about something like that. Will have to check in wiki. I know snowflakes are something found in the high mountains. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 357 total)
You must be logged in to reply to this topic. Login to reply