SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DBA high ended question (5+ years’ experience)


SQL DBA high ended question (5+ years’ experience)

Author
Message
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2539 Visits: 6232
Jeff Moden (8/6/2014)
Actually, that's one of the questions that I frequently ask except it's to a million and the results have to be stored in a table.


I actually sat down to figure out how I'd do this, and I came up with the following:

INSERT INTO #test SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.name) AS num FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B

However, while figuring this out, I had to look up the syntax for ROW_NUMBER(), I forgot that the two tables in the CROSS JOIN had to be aliased if they were identical, and I then had a fun time figuring out why it *still* wouldn't work--it's because I'd enclosed the SELECT in brackets when it shouldn't be! I can only offer as a mitigating factor that I'm a general server admin and don't deal with SQL stuff daily, but I thought I knew T-SQL a bit better than that.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27377 Visits: 13268
paul.knibbs (8/8/2014)
Jeff Moden (8/6/2014)
Actually, that's one of the questions that I frequently ask except it's to a million and the results have to be stored in a table.


I actually sat down to figure out how I'd do this, and I came up with the following:

INSERT INTO #test SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.name) AS num FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B

However, while figuring this out, I had to look up the syntax for ROW_NUMBER(), I forgot that the two tables in the CROSS JOIN had to be aliased if they were identical, and I then had a fun time figuring out why it *still* wouldn't work--it's because I'd enclosed the SELECT in brackets when it shouldn't be! I can only offer as a mitigating factor that I'm a general server admin and don't deal with SQL stuff daily, but I thought I knew T-SQL a bit better than that.


The only remark I have is that you use INSERT INTO, which assumes the table already exists. For quickly creating a tally table, I either use a CTE or SELECT ... INTO.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Louis Hillebrand
Louis Hillebrand
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1624 Visits: 3345
Beatrix Kiddo (8/7/2014)
Or worse "It's Password, with a capital P".

It's Secret
arnipetursson
arnipetursson
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

Group: General Forum Members
Points: 531 Visits: 1019
SQLRNNR (8/7/2014)
sqldriver (8/7/2014)
Eirikur Eiriksson (8/6/2014)
Jeff Moden (8/5/2014)
rajeshn29.dba (8/5/2014)
Hi Friends,
I would like to know SQL DBA high ended question (5+ years’ experience) what kind of questions they will ASK …………………. Just for curiosity
Thanks a lot :
Rajesh


You're the newly hired Sr. DBA for a company. There are no other DBAs. The previous DBA quit a month ago.

Explain in great detail what your first "real" day (normally, the 2nd actual day. The first actual day is usually wasted on paper work and seeing if you can handle a specimen cup without getting your hands wet, etc) will consist of.

On the 3rd "real" day, a database becomes corrupt. What actions will you take?


Another version: On the 3rd "real" day, you are told that a database "may be" corrupt. You look into it and find that it has been so for months....
Cool


First day:

"What's the sa password?"

"It's written on the whiteboard by the developers."

Crying




Go ask the CTO administrative assistant. She has it on a posty note under her keyboard.


It's xxxxxxxx
That way if anyone sees it they think it's been x'd out.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86087 Visits: 41096
paul.knibbs (8/8/2014)
Jeff Moden (8/6/2014)
Actually, that's one of the questions that I frequently ask except it's to a million and the results have to be stored in a table.


I actually sat down to figure out how I'd do this, and I came up with the following:

INSERT INTO #test SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.name) AS num FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B

However, while figuring this out, I had to look up the syntax for ROW_NUMBER(), I forgot that the two tables in the CROSS JOIN had to be aliased if they were identical, and I then had a fun time figuring out why it *still* wouldn't work--it's because I'd enclosed the SELECT in brackets when it shouldn't be! I can only offer as a mitigating factor that I'm a general server admin and don't deal with SQL stuff daily, but I thought I knew T-SQL a bit better than that.


All I can say about that is... EXCELLENT! There are several performance nuances that could be included but the real key is what you did! You took the time to analyze and science out a problem, demonstrated that you can, indeed, find out something that you didn't know, and came up with a great first blush solution that will trounce a WHILE loop or recursive CTE. I love it when people have such intellectual curiosity. Well done, Paul!

Now, pretending that we're in an interview, I'd ask the next question...

With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia, explain or even speculate on what this method could be used for.



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15124 Visits: 18599
GilaMonster (8/6/2014)
Luis Cazares (8/6/2014)
Eirikur Eiriksson (8/6/2014)
benjamin.reyes (8/6/2014)
Eirikur Eiriksson (8/5/2014)
Grant Fritchey (8/5/2014)
Eirikur Eiriksson (8/5/2014)
Guess one has to differentiate between five years of work and five years worth of experience. Questions like "what are the DAC limitations", "consequence of an idle connection holding a lock on a 'popular' resource" or "what are the Resource Governor configuration gotchas?" would make my list. Mind you the answers wouldn't have to be perfect for a 'fiver':-D
Cool


DAC limitations? Not sure. Resource Governor? I know what it is and roughly how it's configured, but I haven't used it. Can I still get the job?

A big part of this comes down to what you're exposed to. I have well over 20 years experience within SQL Server, and, most of it, is real cumulative experience (as opposed to one year of experience multiplied 20 times). But there are giant gaps in my knowledge even so.


Naming one/any DAC limitation would probably do for a fiver, not knowing there are any or then again what DAC is would send them back and erase the agency's number. Resource Governor, that I admit, depends more on the environment but still I think there should be some knowledge and some recognition of keywords.

Don't worry Grant, you would still get the job;-), the point being that there are no simple questions to ask but it's a process of an assessment.
Cool




Which DAC are you asking about?

Interviewing people I really appreciate if they can admit when they don't know something. No one can have deep knowledge about every aspect. Blowhards and liars usually make things worse overall.


Dedicated Administrative Connection, kind of a Stay out of jail thingy
Cool

And the first results from google that refer to BOL show the following. http://msdn.microsoft.com/en-us/library/ee210546.aspx w00t


Yeah, DAC has about 6 meanings these days.

When it comes to an unresponsive SQL Server, how many of those are applicable?
Cool
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15124 Visits: 18599
arnipetursson (8/8/2014)
SQLRNNR (8/7/2014)
sqldriver (8/7/2014)
Eirikur Eiriksson (8/6/2014)
Jeff Moden (8/5/2014)
rajeshn29.dba (8/5/2014)
Hi Friends,
I would like to know SQL DBA high ended question (5+ years’ experience) what kind of questions they will ASK …………………. Just for curiosity
Thanks a lot :
Rajesh


You're the newly hired Sr. DBA for a company. There are no other DBAs. The previous DBA quit a month ago.

Explain in great detail what your first "real" day (normally, the 2nd actual day. The first actual day is usually wasted on paper work and seeing if you can handle a specimen cup without getting your hands wet, etc) will consist of.

On the 3rd "real" day, a database becomes corrupt. What actions will you take?


Another version: On the 3rd "real" day, you are told that a database "may be" corrupt. You look into it and find that it has been so for months....
Cool


First day:

"What's the sa password?"

"It's written on the whiteboard by the developers."

Crying




Go ask the CTO administrative assistant. She has it on a posty note under her keyboard.


It's xxxxxxxx
That way if anyone sees it they think it's been x'd out.

Is that because the ****** is banned by the security policyHehe
Cool
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2539 Visits: 6232
Jeff Moden (8/9/2014)
With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia, explain or even speculate on what this method could be used for.


I would imagine that you're doing this to populate a tally table--I know those are a thing that can be used to make various T-SQL tasks easier and faster, although I can't actually think of an example of using one off the top of my head.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27377 Visits: 13268
paul.knibbs (8/11/2014)
Jeff Moden (8/9/2014)
With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia, explain or even speculate on what this method could be used for.


I would imagine that you're doing this to populate a tally table--I know those are a thing that can be used to make various T-SQL tasks easier and faster, although I can't actually think of an example of using one off the top of my head.


I use it to generate date/time dimensions, for example.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87313 Visits: 45272
paul.knibbs (8/11/2014)
Jeff Moden (8/9/2014)
With the understanding you have of that counting problem and the additional understanding that I don't ask "trick" questions or questions based on trivia, explain or even speculate on what this method could be used for.


I would imagine that you're doing this to populate a tally table--I know those are a thing that can be used to make various T-SQL tasks easier and faster, although I can't actually think of an example of using one off the top of my head.


Give me totals for each day, include the days where there were no rows at all.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search