This is one of the most incredible interviews I’ve had to date. It has plenty of very unique answers and lots of good old-fashioned awkward pause. I’m torn about how much to write down because it really gets kinda long after a while, but I’m likely to put a good deal of it though cause I know some of you will want to hang in there. So without further ado I’ll get to some Q/A.
Oh wait, I suppose I should explain the position first. I’m interviewing candidates for a group downstairs. They’re looking for a SQL guy to help them write reports, and support their current data processes and help support their new SSIS pkgs and do some SSRS stuff. He doesn’t have to know everything, but it would be nice if he had a working knowledge and could be taught. This guy has been doing this for many yrs and has a resume the size of mine. So he’s very experienced.
Q: What does sp_ in front of an sp signify?
A: It means it’s a system procedure supplied by MS.
Me: OK, and what does it do.
Him: Nothing, it just means that MS created it and gave it to you because they thought it was be useful.
Me: And can you create your own?
Him: Well of course. You can create as many as you like.
Me: So does that make them system SPs then?
Him: They’re SPs that were supplied by MS.
Me: Yeah, I got that part. How are they all supplied by MS if I can create my own? Will MS add mine to the product since I created them as system SPs?
Him: Yes. Any SP you add with sp_ will be uploaded to MS and will be included in a service pack.
Me: I don’t think I could have explained that any better myself.
Q: What are the 2 types of UDFs?
A: I didn’t know there were 2 types of UDF.
Me: Ok then.
(Now this one just boggles the mind)
Q: What’s the difference between delete and truncate?
A: I know there’s a difference but I can’t think of it. No wait, I remember. Delete deletes all the data in a table and truncate leaves space at the end.
Me: Pardon? Did you say that truncate leaves space at the end? Then end of what?
Him: Yeah, truncate leaves space but delete gets rid of all the data.
Me: Ok, so what if you only want to get rid of a few rows?
Him: That’s what you call a partial delete and you have to do that inside a transaction.
Me: Moving on…
Q: What are 2 types of cursors?
A: I know there are different types I just don’t know what they are.
(Actually one of the brighter things he’s said.)
Q: What’s the difference between Select Into and Insert Into?
A: Well, I don’t even know where you’re going with that. They’re 2 different statements and they do 2 different things. Yeah, I just don’t know where you’re going with that. It has to be somewhere though. Ok, let me think. A select statement is a query that you use to select data. And an insert you use to put data into a table. But you can use a select with an insert so I guess that makes them the same thing, so I don’t know where you’re going with that. Yeah, I just don’t know. Are you asking me if they’re different, because they’re not. Since you can use a select with an insert that makes them the same thing. Yeah, that’s right.
Me: I think you’ve misunderstood. There’s a select into statement, and there’s an insert into statement. They’re 2 different statements. I’m asking what each one does.
Him: But they’re the same thing aren’t they? Well, I mean except that one selects data and the other inserts it, but you can use them together so doesn’t that make them the same thing?
Me: Well, they both insert data, except the select into creates the table and the insert doesn’t. That’s not the only…
Him: Yeah, I know that. That’s what I was trying to say. Yeah, the select statement creates the table and the insert statement inserts data into it. Yep, that’s right.
Me: Well, that’s not entirely what I said, but let’s move on.
Him: Yeah, it’s close enough. I think we’re talking about the same thing. I’m still not sure where you were going with that though.
Q: What’s the difference between an SP and a trigger?
A: Again, I’m not sure where you’re going with this because a trigger calls an SP so that’s the difference. So a trigger is what calls an SP and the SP is what gets called. That’s the difference.
Me: Ok then. You don’t get any more succinct than that.
Q: What’s the difference between datetime and smalldatetime?
A: I’m not entirely sure what the difference is. I think that datetime only stores small dates and smalldatetime stores larger dates. No, that’s backwards. Datetime stores big dates like anything after say the 15th every month and smalldatetime stores dates from 1 to 14. Yeah, that’s right.
Me: So you’re telling me that I have to have 2 separate cols in my table for each half of the month?
Him: Well yeah. That’s why normalization is such a big deal because if you don’t create your tables properly you’ll be in big trouble because everything’ll be slow.
Me: I honestly don’t know what to say. I’ve never heard that explained in such a clear-minded fashion before. And to tie it back into normalization… just excellent.
(This is my gimme question, right? He’s done so poorly until now I really wanted him to get one.)
Q: What’s the difference between a clustered and a nonclustered index?
A: This is one of those interview questions and I don’t know where you’re going with this one either. I don’t understand this, this is one of those interview questions. This is one of those interview questions…
Me: I’m sorry, you keep saying this is an interview question, what do you mean by that?
Him: Well, this is one of those questions that they ask in every interview. And if you search for interview questions on the internet you usually come up with this one.
Me: So are you telling me that it’s purely an academic question then?
Him: No, I’m just saying that it’s a question you hear in every interview.
Me: Well, this is an interview so why don’t you answer it.
Him: Ok, well a clustered index is a global index. It’s open to everybody. And a nonclustered index is only available to certain people.
Me: I’m afraid I don’t understand.
Him: You don’t have to give permissions for a clustered index; it’s global. Everyone can see it. But for a nonclustered index you need to maintain security for it. It’s part of the MS security model.
Me: So what is it about a nonclustered index that makes you need to lock down security?
Him: I don’t know their reasoning. I just know that’s how it works.
Me: So what’s stored in a nonclustered index that makes you have to lock it down then? What info are you hiding from the general public?
Him: I’ve always wondered that. I don’t know. I just know that it makes the data more secure. That’s what all the documentation says anyway. So I don’t know where you’re going with this because this is just an interview question.
Me: Do yourself a favor and don’t say that again. I get it you think the question’s worthless.
Him: No, it’s not worthless, it’s just that it…
Me: Let’s move on before we get to the end of that sentence.
Q: Explain when you would use the ‘Having’ clause.
A: Well, ‘HAVING’ is not my forte. But it’s used when you can’t use the ‘where’ clause in the right place.
Me: When you can’t use the ‘where’ clause in the right place? I’m not sure what you mean.
Him: There are times when you can’t put the ‘where’ clause where it ordinarily goes so you have to use having.
Me: Can you name one of those times? Give me an example?
Him: Not really. That kind of thing is hard to nail down really. It’s random to begin with.
Me: Wait, what’s random?
Him: When you have to use ‘Having’. It doesn’t happen all the time.
Me: I can see how that would be hard to define something that keeps changing like that. Have you thought about asking MS to make that more stable so people know when to use it?
Him: No, I always figured it was something they couldn’t do or they would have.
Me: Would you be surprised to know that it has been stabilized?
Him: Really? When did they do that? In this last version? Cause I haven’t played with the new one yet.
Me: No, they stabilized it about 25-30yrs ago. I can’t watch you struggle through this anymore. Would you like me to give you the well-kept secret of the ‘having’?
Me: ‘Having’ is like a where clause for a ‘group by’. So if you get a result back from a ‘group by’, say you’re counting how many of each value is in a table with something like Select CustID, count(*) as CT from orders group by CustID, then that’s a result you’re getting back from a ‘group by’. You can see how many orders each customer has had. But if you want to see only customers who have had more than 100 orders, then you would add ‘having count(*) > 100’ and you’ll now only see the ones who have had more than 100 orders. So it’s really kind of a ‘where’ clause for a group by. It doesn’t move. It’s not mysterious. And there’s no secret potions or incantations you have to apply to make it work from one day to the next.
Him: Wow, yeah that makes a lot more sense. See, this is why I need to work here because I feel I could really learn from someone like you.
Me: And furthermore… I’m pretty pissed off that you would even waste my time like this. You’re clearly a complete idiot. In the 20+yrs you’ve been doing this you’ve clearly never once opened a single book. I mean, what the hell kind of moron thinks that this shit is all voodoo that nobody can understand? Computers are solid and predictable and you’re an idiot. I mean, seriously, did you really think that SPs you create get uploaded to MS and included in service packs?
Him: Well I always…
Me: Shut up. I don’t even want to know the answer. I’m afraid some of your stupid will get on me and I’ll step into the street in front of a car. I suggest you do something more suited to your intellect. Just remember, when the buzzer goes off, it’s time to pull the fries.
Ok, I made the part in blue up. I was actually very nice to him. But it sure felt good to say all that here.
So ok, that was my interview yesterday. All of these stupid answers are not only completely real, but they were compounded by his nervous laughter. So for every answer there were a couple senseless jokes and about 30-45secs of him laughing at them.
And I don’t know about you guys, but all of the stupidity aside, I find the bit about some of those being interview questions particularly puzzling and somewhat offensive. That’s implying that these questions are useless and have no place in your day to day life.
So there you go… that’s the interview as best as I can recount it. I was taking some notes while he was talking, but I didn’t do some of them justice. Hell, just not being able to convey his laughing all the time really makes you miss an essential part of the experience. And I’m doing more interviews for this group so there’ll be more coming.