August 8, 2014 at 3:32 am
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.
August 8, 2014 at 3:47 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2014 at 4:03 am
Beatrix Kiddo (8/7/2014)
Or worse "It's Password, with a capital P".
It's Secret
August 8, 2014 at 5:21 pm
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."
: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.
August 9, 2014 at 9:20 am
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
August 9, 2014 at 2:41 pm
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😎
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 :w00t:
Yeah, DAC has about 6 meanings these days.
When it comes to an unresponsive SQL Server, how many of those are applicable?
😎
August 9, 2014 at 2:45 pm
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."
: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 policy:hehe:
😎
August 11, 2014 at 3:02 am
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.
August 11, 2014 at 3:14 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 11, 2014 at 4:22 am
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
August 11, 2014 at 5:38 am
GilaMonster (8/11/2014)
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.
Split a delimited list of values into a table for an injection-proof query.
August 11, 2014 at 7:41 am
Some of these sound more like questions I'd ask of a developer (or a Development DBA, maybe).
August 11, 2014 at 9:28 am
Beatrix Kiddo (8/11/2014)
Some of these sound more like questions I'd ask of a developer (or a Development DBA, maybe).
That too! I do like DBAs that actually know something about T-SQL because not everything is best done by GUI. IMHO, they're also supposed to know something about performance tuning of code and should be able to act as a bit of a mentor to Database Developers.
--Jeff Moden
August 13, 2014 at 9:52 am
August 15, 2014 at 12:29 pm
GilaMonster (8/11/2014)
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.
Give me row identifiers for a Time table (like DimTime for a data warehouse). Or enable me to scrub personal / potentially private information in a database I'm restoring down to a test environment (such as fake bank account numbers).
Or push a load onto a test server to check a query's performance or even leave an open-ended transaction while I mess with other code to see what kind of locks / blocks / performance issues I get.
I can come up with all sorts of reasons. Including "my kitchen timer broke and this is the only method I have for timing my cookies".
Viewing 15 posts - 61 through 75 (of 89 total)
You must be logged in to reply to this topic. Login to reply