Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

SQL DBA high ended question (5+ years’ experience) Expand / Collapse
Author
Message
Posted Friday, August 8, 2014 3:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 1,625, Visits: 5,702
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.
Post #1601089
Posted Friday, August 8, 2014 3:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1601092
Posted Friday, August 8, 2014 4:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 810, Visits: 1,543
Beatrix Kiddo (8/7/2014)
Or worse "It's Password, with a capital P".

It's Secret
Post #1601101
Posted Friday, August 8, 2014 5:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:46 PM
Points: 320, Visits: 961
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....



First day:

"What's the sa password?"

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






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.
Post #1601358
Posted Saturday, August 9, 2014 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 35,575, Visits: 32,168
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1601438
Posted Saturday, August 9, 2014 2:41 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
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'


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.





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

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


Yeah, DAC has about 6 meanings these days.

When it comes to an unresponsive SQL Server, how many of those are applicable?
Post #1601458
Posted Saturday, August 9, 2014 2:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
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....



First day:

"What's the sa password?"

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






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 policy
Post #1601459
Posted Monday, August 11, 2014 3:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 1,625, Visits: 5,702
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.
Post #1601713
Posted Monday, August 11, 2014 3:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 13,539, Visits: 11,342
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1601720
Posted Monday, August 11, 2014 4:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1601752
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse