February 22, 2010 at 8:56 am
Jeff Moden (2/22/2010)
... which one do you think I'll pick?
The one that says pork chop the most :w00t:
February 22, 2010 at 8:57 am
OK Jeff, I did the original problem in about 4 minutes. I thought it was good, but then I read the first 1/3 of your article. I have started the problem over and in about 2 minutes I put your solution into play. For a million records mine was 1:30 your way of thinking is about 10 seconds. and half the code.
Where I work we send an assignment home with the applicants that make it through the first interview and we give them two weeks. We tell them we do not want to see it for two weeks and see what they come back with. We check to see if they have a workable solution first, but then we check the level of the code and see if they tried or just made it work. We have gotten much better programmers this way, because they are not rushed.
John
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]
February 22, 2010 at 8:58 am
J.Faehrmann (2/22/2010)
No offence taken, Jeff. But you made me think about my habits, thanks for that 🙂Being lazy and pragmatic is a very important developer attitude to me.
I never realized that it could look that bad in an interview if the competition was just a bit less lazy.
Now THAT's the spirit, JF! Glad someone gets what I'm trying to say and I do appreciate the feedback.
As a side bar, consider the people that actually are taking offense to what I'm saying here... that attitude will come out in the post-test interview (where we talk about the code they've written) and guess which ones won't fall into the "right fit" category? 🙂 The wrong attitude will definitely show and the interviewer will definitely pick up on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 9:05 am
John Burris (2/22/2010)
OK Jeff, I did the original problem in about 4 minutes. I thought it was good, but then I read the first 1/3 of your article. I have started the problem over and in about 2 minutes I put your solution into play. For a million records mine was 1:30 your way of thinking is about 10 seconds. and half the code.Where I work we send an assignment home with the applicants that make it through the first interview and we give them two weeks. We tell them we do not want to see it for two weeks and see what they come back with. We check to see if they have a workable solution first, but then we check the level of the code and see if they tried or just made it work. We have gotten much better programmers this way, because they are not rushed.
John
Now we're talking! Another person "gets it". I also very much like your weeding "tool"... give them all the time to put their absolute best foot forward. No excuses... just quality code. And thanks for stepping up with the feedback. Hopefully folks will take your feedback and run like hell with it. I also very much appreciate the fact that you DIDN'T post your answer... people need to learn, not just memorize. Very well done, John!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 9:07 am
Steve Jones - Editor (2/22/2010)
Jeff Moden (2/22/2010)
... which one do you think I'll pick?The one that says pork chop the most :w00t:
Heh... nope... the one that brings in the BBQ pack gets extra points! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 9:08 am
Steve Jones - Editor (2/22/2010)
All things being equal, you pick the guy with the set based solution. However I've rarely seen "all things equal" between two candidates in an interview. The better the solution (and quicker), the better you'd score in an interview.The point of the editorial, and this test, however, is to remove those candidates that can't clear a bar.
Again, I agree... it's a good weeding tool. But the smart candidate will turn it into "the winning entry".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 22, 2010 at 9:11 am
Just to annoy Jeff, here's my "ultimate version":
SET NOCOUNT ON;
DECLARE @Table1 TABLE (
ID int IDENTITY PRIMARY KEY,
Number int);
DECLARE @Number INT;
SELECT @Number = 1;
WHILE @Number <= 100
BEGIN
INSERT INTO @Table1 (Number)
SELECT @Number
WHERE @Number NOT IN
(SELECT Number
FROM @Table1);
SELECT @Number = @Number + 1;
END;
DECLARE curNumbers CURSOR GLOBAL DYNAMIC FOR
SELECT DISTINCT Number
FROM @Table1
ORDER BY Number;
DECLARE @Number2 INT, @Number2String VARCHAR(max), @Msg VARCHAR(MAX);
OPEN curNumbers;
FETCH FIRST FROM curNumbers
INTO @Number2;
WHILE @@fetch_status = 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(3 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = CAST(@Number2 AS VARCHAR(MAX));
END;
ELSE
BEGIN
SELECT @Msg = 'Buzz'
END;
END;
ELSE
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = 'Bizz';
END;
ELSE
BEGIN
SELECT @Msg = 'BizzBuzz';
END;
END;
PRINT @Msg;
FETCH NEXT FROM curNumbers
INTO @Number2;
END;
CLOSE curNumbers;
DEALLOCATE curNumbers;
- 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 9:13 am
GSquared (2/22/2010)
Just to annoy Jeff, here's my "ultimate version":
SET NOCOUNT ON;DECLARE @Table1 TABLE (
ID int IDENTITY PRIMARY KEY,
Number int);
DECLARE @Number INT;
SELECT @Number = 1;
WHILE @Number <= 100
BEGIN
INSERT INTO @Table1 (Number)
SELECT @Number
WHERE @Number NOT IN
(SELECT Number
FROM @Table1);
SELECT @Number = @Number + 1;
END;
DECLARE curNumbers CURSOR GLOBAL DYNAMIC FOR
SELECT DISTINCT Number
FROM @Table1
ORDER BY Number;
DECLARE @Number2 INT, @Number2String VARCHAR(max), @Msg VARCHAR(MAX);
OPEN curNumbers;
FETCH FIRST FROM curNumbers
INTO @Number2;
WHILE @@fetch_status = 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(3 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = CAST(@Number2 AS VARCHAR(MAX));
END;
ELSE
BEGIN
SELECT @Msg = 'Buzz'
END;
END;
ELSE
BEGIN
SELECT @Number2String = CAST(@Number2 AS DECIMAL(6,3))/CAST(5 AS DECIMAL(6,3));
WHILE @Number2String LIKE '%.%'
BEGIN
SELECT @Number2String = RIGHT(@Number2String, LEN(@Number2String)-1)
END;
IF CAST(@Number2String AS BIGINT) > 0
BEGIN
SELECT @Msg = 'Bizz';
END;
ELSE
BEGIN
SELECT @Msg = 'BizzBuzz';
END;
END;
PRINT @Msg;
FETCH NEXT FROM curNumbers
INTO @Number2;
END;
CLOSE curNumbers;
DEALLOCATE curNumbers;
...well that last candidate wasted my time!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 9:15 am
Jeff Moden (2/21/2010)
Heh... I just Googled "FIZZBUZZ SOLUTIONS SQL"... I'm amazed and surprised that some of those folks had the nerve to publish some of the solutions they have. Maybe it's a good test after all.
First I tried writing a set-based solution which had to generate its numbers table (so that it would scale beyond my pre-existing tally table) and the I tried writing a loop-based one, just to see which I could write quicker. They both took about the same time to write. I must have been reading too much of your stuff Geoff, because I am not really an SQL programmer!
Then, seeing your comment, I googled it too - just to see what was out there. The first one that came up had me laughing myself silly: some poor lad had produced a poor solution (using a recursive CTE to generate the numbers table) and was advised by an "expert" to change it to an appalling solution (using the number column of master.dbo.spt_values, which is totally unscalable beyond 2048). The next thing I found was someone asking for advice on what would be a decent T-SQL equivalent of FizzBuzz; the answers were pretty varied, and didn't include what is, in my opinion, the obvious one: use FIZZBUZZ with one miniscule change: instead of 100 write 10000000 - anyone who writes a loop for that is a waste of space! I decided that I'd had enough amusement and didn't look at any more of the search results.
Of course the best way of making FIZZBUZZ into a genuine T_SQL test is to ask someone to write a stored procedure that takes the limiting number as a parameter - so they have to cope with whatever number is passed in. That eliminates the use of a pre-generated Tally table (otherwise of course for 100 (indeed, up to 11000) I could just use my permanent Tally table).
By the way, there was method in choosing a hundred million as a limit - it's big enough to need a 3-way cross join of master.sys.all_columns to generate the CTE, and small enough that limiting the size of that cross join (by not cross-joining the whole table to itself, but joining part of the table to itself) is useful.
Tom
February 22, 2010 at 9:18 am
Gary Varga (2/22/2010)
...well that last candidate wasted my time!!!
C'mon, I'm sure you'd end the interview after
DECLARE curNumbers CURSOR
How long could that take?
February 22, 2010 at 9:24 am
Steve Jones - Editor (2/22/2010)
Gary Varga (2/22/2010)
...well that last candidate wasted my time!!!C'mon, I'm sure you'd end the interview after
DECLARE curNumbers CURSORHow long could that take?
Steve,
I must have posted here too often. I agree with what I am certain you are implying - yep, the use of cursors - however, anyone who thinks that curNumbers is a valid name will have the door open for them due to the risk that they are unable to use a handle themselves.
I make no apologies G-Squared. Naming in Software Engineering is more important than most, if not everything, so far mentioned in this thread.
Jeff may get on his soapbox once in a while but I for one applaud him for it.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 22, 2010 at 9:27 am
Gsquared your "ultimate version" is much fun to read!
Now you only need to tell Jeff that you always code like that 😛
February 22, 2010 at 9:27 am
Steve Jones - Editor (2/22/2010)
Gary Varga (2/22/2010)
...well that last candidate wasted my time!!!C'mon, I'm sure you'd end the interview after
DECLARE curNumbers CURSORHow long could that take?
Steve! C'mon! It shouldn't even get that far!
Declaring the table variable should be as far as that one goes. Not because it's a table variable, but because it's got a surrogate key that should actually have the value you want for the row! Just change the first column so it doesn't have an identity property, and get rid of the second column completely.
Then it populates it with a while loop that includes a "where not in" that's completely unnecessary! We're talking serious performance hits there on anything with enough rows to matter. Add that it's on a non-indexed column in a table variable, and we're looking at a Nested Loops operation on a table scan.
Both of those are before declaring the cursor. Shouldn't even get that far before spewing soda all over the screen in shock! 🙂
This was meant to be a masterpiece of garbage T-SQL. The fact that it's got a cursor in it is a minor detail.
- 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 9:31 am
You are correct, the declare table should stop things. I might be curious to see where someone was going, but the declare cursor would drop someone way down my list immediately.
For the record, I think Jeff is correct. You write the best code you can, and for a DBA that means set-based. The two things I'd think of write away are the WHILE loop, and the recursive CTE, which I know isn't any better, and IMHO, is harder to read. I can't compete with Jeff on the set based one, and in an interview I'd be aiming for points by a) solving the issue and b) solving it quickly.
I still think I'd beat Jeff with a winning smile, a Friday shirt, and an extra Starbucks card I could leave on the table 😀
February 22, 2010 at 9:33 am
If you're going to setup your table with a while loop, just for a set-based solution, wouldn't the direct while loop be quicker without the table? Such as :
declare @i int
select @i = 1
while @i <= 100
begin
if (@i % 3 = 0) and (@i % 5 = 0 )
print 'fizbuz'
else if (@i % 3 = 0)
print 'fiz'
else if (@i % 5 = 0)
print 'buz'
else
print @i
select @i += 1
end
Wouldn't this be faster?
Viewing 15 posts - 46 through 60 (of 363 total)
You must be logged in to reply to this topic. Login to reply