February 22, 2010 at 12:31 pm
Sean-752587 (2/22/2010)
Jason and JeffAs far as scaleability is concerned, I did try it with 1 Mil. rows which didn't work in my master database (SQL 2008 dev edition). It topped out at 434281 rows.
It did however work in our dev database on a test server. It ran in a little over 18 seconds.
Even if there aren't enough rows in the source tables, what do you think of a 2nd cross join?
...
from sys.columns sc1,
sys.columns sc2,
sys.columns sc3
What do you get when you run the following code???
SELECT COUNT(*)
FROM Master.sys.All_Columns
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 12:38 pm
Sean-752587 (2/22/2010)
WayneS (2/22/2010)
Sean-752587 (2/22/2010)
Hi jcrawf02How would you change it to avoid the cast?
like this... you don't need to cast ROW_NUMBER to an int... it already is
select top 100
case
when ROW_NUMBER() over (order by sc1.name) %3 = 0
and ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'FizzBuzz'
when ROW_NUMBER() over (order by sc1.name) %3 = 0 then 'Fizz'
when ROW_NUMBER() over (order by sc1.name) %5 = 0 then 'Buzz'
else cast(ROW_NUMBER() over (order by sc1.name) as varchar(8))
end
from sys.columns sc1,
sys.columns sc2
He who can read...
Thanks. Looks like a typical copy and paste error.
Shouldn't have happened though.
I really love the cross join on this. In our master.sys.all_columns table, we have over 6000 rows, so a cross join privides a nice size array.
February 22, 2010 at 12:57 pm
how about (results in text)...
SELECT
CASE
WHEN (i % 3=0 AND i % 5=0) THEN 'FizzBuzz'
WHEN i % 5=0 THEN 'Buzz'
WHEN i % 3=0 THEN 'Fizz'
ELSE CAST(i as VARCHAR(5))
END AS i
FROM(
SELECT TOP 100 Row_number() OVER(ORDER BY [NAME]) AS i
FROM master..syscolumns
)x
it won't scale much, but that wasn't part of the original spec
🙂
February 22, 2010 at 1:03 pm
Ivanna Noh (2/22/2010)
it won't scale much, but that wasn't part of the original spec🙂
Writing scalable code should be an "implied" requirement for all Developers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 1:07 pm
Jeff Moden (2/22/2010)
Ivanna Noh (2/22/2010)
it won't scale much, but that wasn't part of the original spec🙂
Writing scalable code should be an "implied" requirement for all Developers. 😉
...didn't see maintainable nor robust either!!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 1:10 pm
Fair enough Jeff...this will scale up to 400,000 (on our system). Further joins would increase that number:
SELECT
CASE
WHEN (i % 3=0 AND i % 5=0) THEN 'FizzBuzz'
WHEN i % 5=0 THEN 'Buzz'
WHEN i % 3=0 THEN 'Fizz'
ELSE CAST(i as VARCHAR(5))
END AS i
FROM(
SELECT TOP 100 Row_number() OVER(ORDER BY s1.NAME) AS i
FROM master..syscolumns s1
INNER JOIN master..syscolumns s2
ON s1.NAME = s2.NAME
)x
...must be late @night over there. Can't sleep?
😉
February 22, 2010 at 1:17 pm
scalable, maintainable and robust...not in the original spec!
if that was a contract i'd be looking for additional payment for variations :w00t:
This was meant as a quick coding test...a rough gauge... wasn't it?
February 22, 2010 at 1:21 pm
Ivanna Noh (2/22/2010)
scalable, maintainable and robust...not in the original spec!if that was a contract i'd be looking for additional payment for variations :w00t:
This was meant as a quick coding test...a rough gauge... wasn't it?
No. A demonstration of your skills, knowledge and processes. Otherwise you will hear, if anything, that "one of the other candidates had more relevant skills".
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 1:25 pm
ok - but does the code look ok?
I welcome constructive criticism 🙂
February 22, 2010 at 1:26 pm
I had one interview where they gave me (I think it was) a half-hour to answer ten programming questions. (It was sufficiently long enough ago that I don't remember the details of the test.)
I do, however, remember the interviewer asking me, after about 25 minutes, how I was doing. I told him that I was working on the last question.
The interviewer told me, "that's pretty good. Most people don't get past question 3 or 4."
That led me to wonder: who were these other people who claimed to be "programmers?"
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 22, 2010 at 1:49 pm
Ivanna Noh (2/22/2010)
ok - but does the code look ok?I welcome constructive criticism 🙂
I think you want to change your INNER JOIN to a CROSS JOIN (which means remove the ON and everything after it on that line).
Otherwise, your code satisifies the requirements.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 22, 2010 at 1:49 pm
Ray K (2/22/2010)
I had one interview where they gave me (I think it was) a half-hour to answer ten programming questions. (It was sufficiently long enough ago that I don't remember the details of the test.)I do, however, remember the interviewer asking me, after about 25 minutes, how I was doing. I told him that I was working on the last question.
The interviewer told me, "that's pretty good. Most people don't get past question 3 or 4."
That led me to wonder: who were these other people who claimed to be "programmers?"
I had an interview like this a couple of years back (unless I'm very badly mistaken - with one of the regular contributors on this board!). It was one of the better interviews I'd been involved with: that was just the round #1, just to see if you could advance on and "play in the Double Jeopardy round".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2010 at 1:53 pm
Ray K (2/22/2010)
I had one interview where they gave me (I think it was) a half-hour to answer ten programming questions. (It was sufficiently long enough ago that I don't remember the details of the test.)I do, however, remember the interviewer asking me, after about 25 minutes, how I was doing. I told him that I was working on the last question.
The interviewer told me, "that's pretty good. Most people don't get past question 3 or 4."
That led me to wonder: who were these other people who claimed to be "programmers?"
I had an interview where I "failed" the test because the interviewer had never seen some of the T-SQL code I used to handle those questions.
For example, part of one of the questions asked for removing the time from a date, and I used the nested DateAdd, DateDiff method, since it's the fastest. He didn't like that answer because he was looking for a conversion to varchar using style 101 and then a reconversion to datetime. He didn't understand what my answer was doing, so he assumed I didn't know what I was talking about. I tried to explain it to him, but he wasn't interested.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2010 at 2:08 pm
Well I have to dispute the scalability of Jeff's implied solution and also think it is clunky and not intuitive. If we are to add scalability and speed as additional constraints, as Jeff has suggested, his solution succeeds on speed and fails on scalability. I prefer the obvious WHILE solution and would not be concerned about speed for the question as originally stated.
I cringe at the idea of relying on the # of recs in Master.dbo.SysColumns. But there are times when you make design decisions based on your impression of the problem. Sometimes to meet a real world constraint, you will do something that is ugly because there are no better alternatives.
But let's face it, this is a trivial problem. And while one person may say loops are bad here and another may want %15 instead of %3 and %5 together, these can have little to do with solving real world problems, though the thinking involved can at times have an important place in solving real world problems. Developing systems is more about understanding a lot of things and a vision and how it comes together and what constraints are real that one must meet. And if something is taking too long, then you look at it and come up with a solution. The rest is just for show, if one has too much time on their hands.
I do thank Jeff for showing us his way and the speed improvement of not using a loop.
February 22, 2010 at 2:27 pm
reidres (2/22/2010)
Well I have to dispute the scalability of Jeff's implied solution and also think it is clunky and not intuitive. If we are to add scalability and speed as additional constraints, as Jeff has suggested, his solution succeeds on speed and fails on scalability. I prefer the obvious WHILE solution and would not be concerned about speed for the question as originally stated.I cringe at the idea of relying on the # of recs in Master.dbo.SysColumns. But there are times when you make design decisions based on your impression of the problem. Sometimes to meet a real world constraint, you will do something that is ugly because there are no better alternatives.
But let's face it, this is a trivial problem. And while one person may say loops are bad here and another may want %15 instead of %3 and %5 together, these can have little to do with solving real world problems, though the thinking involved can at times have an important place in solving real world problems. Developing systems is more about understanding a lot of things and a vision and how it comes together and what constraints are real that one must meet. And if something is taking too long, then you look at it and come up with a solution. The rest is just for show, if one has too much time on their hands.
I do thank Jeff for showing us his way and the speed improvement of not using a loop.
Heh... not bad for "clunky", huh?
I believe you may be missing the point... how you solve a trivial problem will be a good reflection of how you solve one that's not. It's also not just one person saying that (generally) loops are bad.
BTW, the version that I've not shown you will take an INT to the max and it does it without referencing any tables at all. I don't expect people to get to that level on an interview for just a Sr. Developer, though. I do, however, expect them to be able to avoid the loop and recursion and I do expect it to be scalable to at least a million rows.
I'm also not looking for "intuitive". Bloody loops are intuitive. Set based isn't necessarily but it's the way to go. Of course, if you're actually good at set based code, it WILL be intuitive. 😉
I prefer the obvious WHILE solution and would not be concerned about speed for the question as originally stated.
And that is the type of person I'm trying to weed out. I want everyone on my team to be concerned about speed (and, of course, accuracy) at all times... even for the trivial things.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 91 through 105 (of 363 total)
You must be logged in to reply to this topic. Login to reply