The T-SQL Quiz
-
I agree with your assessment that checking for "x % 3 = 0 and x % 5 = 0" is equivalent to checking for "x % 15 = 0". "x % 15 = 0" is also more efficient. Your idea also led to the idea of generalizing the algorithm, which requires calculating the Least Common Multiple of the two numbers, which led to some more interesting code.
All that is to say you never know where these conversations will lead, and there's always a good idea, new algorithm, or Monty Python reference waiting to be discovered right around the corner. That's why I love these discussions
-
PS How do you get the code to format in this editor?
You either have to do it manually, or you might try one of these:
http://www.simple-talk.com/sql/sql-tools/the-simple-talk-sql-prettifier/
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
Then copy and paste into here, in HTML edit mode.
-
Heh... relax, Scott... we're all having a bit of fun here. Grant's post was great for stirring the pot... haven't seen this much going on in a long time. It's fun to watch.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You mean this query???
Declare @Counter int
Set @Counter = 1
While @Counter <= 1000000
Begin
Print Case When @Counter % 15 = 0 Then 'BizzBuzz'
When @Counter % 5 = 0 Then 'Buzz'
When @Counter % 3 = 0 Then 'Bizz'
Else Str(@Counter)
End
Set @Counter = @Counter + 1
End
That's one heck of a lap top... it takes 3 minutes and 8 seconds and pegged the cpu the whole time on my desktop which is a 1.8 Ghz Single Cpu with 1 Gig of ram and twin 80 IDE harddrives. You sure you didn't make some other change? Sure whould like to see the code you ran for the million row example...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Nice job again. Looking for work?
Nah... Scott would think I'm looking for a cure for cancer
Just kidding. On the serious side, this was a great post... look at all the side-bars this broke out... lot's of good people with lot's of ideas and a couple of rants (mine included). Haven't seen a post stir up so much in a while and it's great to see... kinda like asking folks who has the best chain saw when you're really trying to just mow the lawn
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have to say I was really surprised at how much response this little article generated. I dashed it off after we had our fun with it. I didn't think Steve would even post it because it was so trivial. Kind of makes me crazy though because I've put a lot of work into the posts on unit testing and they didn't generate a tenth as much interest. Wild stuff.
Oh, and Husquavarna. I've got the 359. Use it every week to cut wood for the furnace. Best damn chainsaw anywhere. Now get back to your lawn.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What a great thread. Thanks to all for contributing to a tremendous read.
Dave J
-
Takes 15 seconds on my laptop. Dual-core x64 (1.8 ghz, I think?) with 2 GB of RAM, running Vista/SQL 2005 SP2.
--
Adam Machanic
whoisactive -
Update:
I just ran it in OSQL from a command prompt and it took 1 minute, 28 seconds. So it seems that the real issue here is not time to do the calculations, but time to display the output... Jeff, I gather you're running SQL Server 2000? Are you running these queries in QA? Perhaps SSMS is faster at displaying messages or something (it must be faster in SOME WAY
)--
Adam Machanic
whoisactive -
Here is mine. I did it without % because I couldn't find the mod function by name in Books Online and did not want to give in and go to Google. I searched for 'mod', 'modulus', and under Mathematical Functions and (after seeing the examples) under '%' all without success. It turns out that the entry is under '% modulo'.
As a result, it took me a lot longer, and I don't know if looking for the decimal is reliable in all cases, but it worked when I ran in it in SQL 2000. Here it is, warts and all. 🙂 Two things in support of my effort, though: (1) I did take less than 10 minutes to do it in ColdFusion since I knew the function for mod and (2) I saw at once that I would have to check for the case of divisible by both 3 and 5 first, or else the if statements would miss that case.
That's a great puzzle - not too hard or too easy. Thanks!
--T-SQL
DECLARE @count int
DECLARE @DivResultBy3 float
DECLARE @DivResultBy5 float
SET @count = 1
WHILE @count <= 100
BEGIN
SET @DivResultBy3 = CAST(@count / 3.0 AS varchar(10))
SET @DivResultBy5 = CAST(@count / 5.0 AS varchar(10))
IF (CHARINDEX('.',@DivResultBy3) = 0
AND CHARINDEX('.',@DivResultBy5) = 0)
PRINT 'BizzBuzz'
ELSE
IF CHARINDEX('.',@DivResultBy3) = 0
PRINT 'Bizz'
ELSE
IF CHARINDEX('.',@DivResultBy5) = 0
PRINT 'Buzz'
ELSE
PRINT @count
SET @count = @count + 1
END
I also did it in ColdFusion, for what it's worth:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Programming Quiz</title>
</head>
<body>
<!--- I was reading the latest Simple-Talk email that linked me to an article on Coding Horror, "Why Can't Programmers - Program?" In the article they talked about a simple test that they gave developers to verify their abilities and decide whether or not to continue the interview. Here's the test: Write code that counts from 1 to 100 For each number evenly divisible by 3, substitute 'Bizz' For each number evenly divisible by 5, substitute 'Buzz' For each number divisible by both substitute 'BizzBuzz' --->
<cfloop index="count" from="1" to="100">
<cfif (count mod 3 is 0) and (count mod 5 is 0)>
BizzBuzz<br>
<cfelseif count mod 3 is 0>
Bizz<br>
<cfelseif count mod 5 is 0>
Buzz<br>
<cfelse>
<cfoutput>#count#</cfoutput>
<br>
</cfif>
</cfloop>
</body>
</html>
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Just be aware that FLOAT uses an "approximate" IEEE representation to store values (no flames please, "approximate" is from the ISO standard), and using it can cause problems - especially when trying to compare FLOAT values for exact matches. You might run into the occasional 'wtf' with FLOATs
-
Hello Sergiy,
I agree with you in general, with the following elaboration. I think it depends on what the interview questions are asking. I wouldn't dismiss someone who got an answer that was inefficient in terms of scalability if the question is focusing on getting the answer quickly. At the same time, Jeff Moden's solution is very elegant and -- assuming that he could do it within any time limit specified by the prospective employer -- deserves a lot of credit for its scalability and documentation. I'd certainly take a second look at someone who was able to do both -- that is, Jeff's solution in a short amount of time (maybe 10-15 minutes instead of 2).
I think two-minute tests may have a certain "wow" factor to use in stories about tech gurus, but in the real world if someone can do something as well as what Jeff showed in under 30 minutes, AND they can manage their time and work relationships, then that is more than enough to succeed. I would also be impressed if someone spent a little more (but not too much more) time than expected and developed something really innovative or extremely efficient.
Finally, although set-based solutions are the way to think in SQL, again, someone should not necessarily get points off if they first find a loop solution that works. Anyone who deals with SQL applications should know looping constructs because sometimes that is the only way to handle certain logic within applications. So I would not penalize someone for successfully implementing a loop, even if it is inefficient compared to a SQL solution, unless you know that for the specific job that looping would not be relied upon. Although, I think that kind of job is probably rare in the database world. Even if one rejects looping as much as possible, one still needs to know enough about the loops to know what they are doing.
However, I would give extra credit for someone who did what Jeff did and could clearly explain the benefits of the set-based approach. And I'd also give credit for the documentation, about which Jeff made a very good point. In both cases, though, I would not blindly give more credit to the set-based solution if the person could not explain why they decided to use it. The only constructive criticism I can offer (and Jeff seems to have anticipated it by apologizing for a "rant") is that one should also acknowledge some humility. Yes, Jeff's answer was more efficient, and yes the question didn't say you couldn't return a result set instead of just printing. But sometimes it is more important to gently go back to the client and ask again about something that appears not to have been specified rather than looking for ways to insert your preferred way of doing something if it is not explicitly forbidden by the stated requirements. As I said, though, that is constructive criticism, and it is almost always better to hire someone who thinks a lot about efficiency and documentation than someone who doesn't.
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Thanks!
---
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
This is great. I mentioned you in one of my other posts, a reply to Sergiy, and I hope you read my comments.
By the way, I don't know if this is yet another efficiency, but I changed your CAST statements to use varchar(8) instead of varchar(10) since 8 (BizzBuzz) is the longest string you will store.
I ran your code with varchar(10) and it ran in 12 seconds on my test server. With varchar(8) it ran in 6-8 seconds.
Something to think about.
Thanks,
webrunner
----
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 91 through 105 (of 309 total)
You must be logged in to reply to this topic. Login to reply