Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Before going to interview with Microsoft

Author  Topic 

sanjnep
Posting Yak Master

191 Posts

Posted - 2005-08-01 : 13:59:14
Hi All,
If any one is going to take interview with Microsoft please read data structure like link list, stack, queue, binary tree etc. Manager will ask these questions even you are applying for sql server developer or sql server DBA position.
Thanks
Sanjeev

Sanjeev shrestha

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 14:00:36
great to know...
did you make it?

Go with the flow & have fun! Else fight the flow
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 14:10:04
quote:
Originally posted by sanjnep

Hi All,
If any one is going to take interview with Microsoft please read data structure like link list, stack, queue, binary tree etc. Manager will ask these questions even you are applying for sql server developer or sql server DBA position.
Thanks
Sanjeev

Sanjeev shrestha



As a hiring manager in the SQL dev org at Microsoft, I'd advise you to be able to explain why you're choosing to apply these in a particular circumstance. BTW - I never ask for definitions of these structures because they're commonly known as obvious interview question. Sometimes I've asked for the sequence of operations to do a crash-proof split of a b-tree leaf node (including what gets logged and when) or how to do a parallel b-tree rebuild using sub-btrees stitched together. One of my favorites is also to derive an algorithm for defragging an index (i.e. DBCC INDEXDEFRAG that I wrote).

Did you get an offer?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 14:49:54
quote:

One of my favorites is also to derive an algorithm for defragging an index (i.e. DBCC INDEXDEFRAG that I wrote).



do you at least give them some pointers???


Go with the flow & have fun! Else fight the flow
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-01 : 14:56:33
Yikes!

Would that be hiring for a developer of sql server or a sql server developer? I consider myself a halfway decent t-sql/database developer but have no clue how to answer those questions.

What happended to the good ole days when the obvious interview question was, "Whats the difference between an inner and an outer join?" Oh well, I guess working for Paul is out of the question...I'll just have to count on my other goal of getting good enough to compete in the PGA's senior tour. (just as likely)

Be One with the Optimizer
TG
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-01 : 15:07:47
I like to ask people non-technical off the wall problem solving stuff that does not have a real answer just to engage thought processes and observe reaction.

why is a man-hole cover round?







====================================================
Regards,
Sean Roussy

GENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-01 : 15:12:07
ok, I'd have a chance with you Sean. So it cant be dropped into the hole its covering.

Be One with the Optimizer
TG
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 15:16:05
not all of them are round here.
but you know... i'd never think of that... ok maybe i would...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-01 : 15:20:42
I score on originality and reaction mostly. If someone gets angry or annoyed after a few of these I send them out the door post haste. People who respond with questions or something I have'nt heard before get good marks too.

====================================================
Regards,
Sean Roussy

GENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 15:25:07
you're telling me there are people who come for an interview in IT business and aren't thrilled to tackle a problem???
wow... didn't think they exist...

i mean come on... if you can't think of a solution you ask for help... you don't get angry...do you?!?!?????

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-08-01 : 15:50:05
>>>why is a man-hole cover round?
<<<

Because the man-hole itself is round!

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-01 : 15:53:48
One my favorites was "because a round man-hole cover requires less material than a square one to produce because you get to cut the corners off"

====================================================
Regards,
Sean Roussy

GENERAL ADVICE FOR EVERYONE: Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 15:55:31
quote:
Originally posted by TG

Yikes!

Would that be hiring for a developer of sql server or a sql server developer? I consider myself a halfway decent t-sql/database developer but have no clue how to answer those questions.



Developer in the Storage Engine team of SQL Server - very different set of skills and knowledge from developer using SQL Server (although I think I could come up with some great questions there too!)


Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 16:40:10
please do paul... i'd love to hear them.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-01 : 16:58:09
quote:
Originally posted by spirit1

please do paul... i'd love to hear them.

Go with the flow & have fun! Else fight the flow



Off the top of my head:

1) list the transaction isolation levels supported by SQL Server and describe when the use of each is appropriate
2) define index fragmentation and when its appropriate to remove it
3) explain how to make use of minimal logging mode when loading data into SQL Server
4) explain how to examine the query plan choice and look for performance problems
5) explain how various relational constraints are physically manifested by SQL Server
6) describe how to allow the use of AVG with an indexed view (just posted on this here yesterday)
7) when does SQL Server honor locking hints?
8) what is a deadlock and how do you debug it? What about a live-lock? What are the best practices for avoiding deadlocks?
9) what is proportional fill?
10) what datatypes are not recommended as index keys? Why not?
11) what is a merry-go-round scan?
12) what data validation checks are performed using RPC?
13) why is shrinking a database not a good idea?
14) what is cardinality estimation? List 5 ways to screw it up.
15) what is read-ahead?

Enjoy

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 01:21:34
OK, I'm gonna try for a job:

1) Default - that works for me

2) I use DBCC SHRINKFILE (MyLogName, 1) to prevent fragmentation - because if the file is really short it can't be fragmented can it?

3) I DROP all TRIGGERS because they cause more transaction logging during data loading

4) I don't know the answer to that. But if a Query is slow I replace it with a CURSOR, that usually works. I've heard that using a Stored Procedure can help with performance, but I've never had to use one yet.

5) They physically manifest as Foreign keys - or did you mean me to say "Use JOINS" ?

6) Ha! Excellent, I've spotted the trick question. AVG is allowed on any column, it doesn't have to be indexed - or even in a VIEW

7) It always honors them - I normally use (nolock) though to stop it doing it.

8) A deadlock is where you do UPDATE A ... then UPDATE B ... and your mate does B first then A. (We've solved this where I'm working now by always doing them in alphabetical order - and where that effects the referential integrity of our application we rename the tables to get the earlier one first in the alphabet)

9) This is where the maintenance plan thingie changes the FILL FACTOR on a table from a perfectly reasonable 100% to 90% without asking you.

10) I have heard that using BIT is a bad idea in an index because it is not very "selective" - but it seems to work when I use a bit column in a SELECT statement, so I'm not sure why. But anyway, 'coz I read it was bad I don't do it.

11) You got me beat on this one. Trying to spot a pretty girl at a fairground ride?

12) That's when you call a stored procedure remotely, isn't it? I've never called one in someone else’s database, so I dunno. I could look it up though, I've got "BoL"

13) But it IS a good idea - I explained earlier how it cuts out the fragmentation by keeping the files small

14) Billy, John, Charlie, Bruce and Dickie. They are your best 5 ways to screw up anything. Their code is rubbish.

15) That's where SQL pulls in more data than it needs. It’s a bug that Microsoft hasn’t fixed yet, and it’s a pain because it wastes CPU cycles.

Enjoy

Kristen
Go to Top of Page

Westley
Posting Yak Master

229 Posts

Posted - 2005-08-02 : 03:26:12
I might have a go as well...will we get answers for it? hehe

1. read uncommitted (dirty read), read committed, repeatable read, serializable, you will use dirty read if u don't care about the data change while reading, read committed does care etc etc

2. dbcc showcontig will shows, when index frag up to a point u need to run dbcc dbreindex to fix it, help performance as well.

3. put the DB to bulk insert recovery mode, done with your inserts/loading, then put it back to full (if required)

4. look for table scan, index scan, try to put in index (or cover index) to solve those issue

5. foreign keys, primary keys

6. have no idea, will need to look it up...:)

7. always, when u put the hint in the query

8. deadlock happens when 2 queries waiting for each other for resources, debug it? kill 1 of them..:) live-lock happens when u do a read which locks the data but others can still read, best prac to avoid deadlock is keep all your transaction in the same seq, which will generally avoid deadlock.

9. fill factor for an index, describle how much free space an index is having or allow to have

10. bit, cos index is in b-tree format, bit doesn't help at all..:)

11. never hear of it...haha

12. donno

13. generally when u go out of space for the file, its when the db is busy, if it needs to get more space from the OS which will make it even worst, so keeping enough free space should avoid some overhead of getting space from OS.

14. donno

15. donno much about it, all i know is it actually read more then what it ask for, and if the page is line up, then it will save time to get the next set of data cos it'll be in cache already, which speeds up the performance.

hope others can fill in what i donno, and hey Paul, will I get the job? hehe

Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-02 : 12:24:01
quote:
Originally posted by Kristen

OK, I'm gonna try for a job:

1) Default - that works for me

2) I use DBCC SHRINKFILE (MyLogName, 1) to prevent fragmentation - because if the file is really short it can't be fragmented can it?

3) I DROP all TRIGGERS because they cause more transaction logging during data loading

4) I don't know the answer to that. But if a Query is slow I replace it with a CURSOR, that usually works. I've heard that using a Stored Procedure can help with performance, but I've never had to use one yet.

5) They physically manifest as Foreign keys - or did you mean me to say "Use JOINS" ?

6) Ha! Excellent, I've spotted the trick question. AVG is allowed on any column, it doesn't have to be indexed - or even in a VIEW

7) It always honors them - I normally use (nolock) though to stop it doing it.

8) A deadlock is where you do UPDATE A ... then UPDATE B ... and your mate does B first then A. (We've solved this where I'm working now by always doing them in alphabetical order - and where that effects the referential integrity of our application we rename the tables to get the earlier one first in the alphabet)

9) This is where the maintenance plan thingie changes the FILL FACTOR on a table from a perfectly reasonable 100% to 90% without asking you.

10) I have heard that using BIT is a bad idea in an index because it is not very "selective" - but it seems to work when I use a bit column in a SELECT statement, so I'm not sure why. But anyway, 'coz I read it was bad I don't do it.

11) You got me beat on this one. Trying to spot a pretty girl at a fairground ride?

12) That's when you call a stored procedure remotely, isn't it? I've never called one in someone else’s database, so I dunno. I could look it up though, I've got "BoL"

13) But it IS a good idea - I explained earlier how it cuts out the fragmentation by keeping the files small

14) Billy, John, Charlie, Bruce and Dickie. They are your best 5 ways to screw up anything. Their code is rubbish.

15) That's where SQL pulls in more data than it needs. It’s a bug that Microsoft hasn’t fixed yet, and it’s a pain because it wastes CPU cycles.

Enjoy

Kristen



I actually believed you were serious for the first few answers

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-02 : 12:32:20
joking aside...
paul can you maybe also provide the correct answers??
because for some i have no idea...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

KLang23
Posting Yak Master

115 Posts

Posted - 2005-08-02 : 12:37:39
>>>why is a man-hole cover round?<<<

So there is absolutely no ambiguity regarding proper use.
Otherwise, there would be a forum.



Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-02 : 12:39:13
quote:
Originally posted by Westley

I might have a go as well...will we get answers for it? hehe


No answers yet. You got 1, 3, kind of 4, deadlock part of 8, one example for 10, 13, 15 right. Better than Kristen


Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page
    Next Page

- Advertisement -