FizzBuzz

Question

FizzBuzz

Viewing 15 posts - 16 through 30 (of 363 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Richard is absolutely correct.

Although I'd like to see a tribunal oversee a heated debate on coding standards - I'd be killing myself!!!

Seriously though, it is an important issue that can no longer be talked about above board. As with any kind of censorship, it does not eradicate it, it just moves underground.....allegedly

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 2

reidres (2/22/2010)
It took me 14 minutes and I developed a solution which turned out to closely approximated one or more of what was on Fritchey's page.
What is wrong with using a WHILE and what would be a preferable approach? Also, did you learn of this in a book or where?
Thank you.

I can tell you... SQL Server is a "set based" / declarative language where you tell it what you want to do to a "set" of information and it figures out (usually) the best way to execute the code. Overriding that with an explicit loop will almost always (notice I said "almost", there are rare instances where this is not true) result in code that's a lot slower and a whole lot more resource hungry.

To be specific, when I'm hiring someone for a database Developer position or even a GUI Developer who will be touching a database a lot (and most do), I'm not looking for a casual database user that will use slow procedural code for such a simple problem. I'm looking for someone who can leverage both the machine and the language. I also want it to be fully commented, very well formatted code because I want to see what their programming style is. There's nothing worse than hiring someone well studied and capable that's either too lazy or too arrogant to write production quality code. In other words, do they embrace the mantra "Make it work, make it fast, make it pretty and it ain't done 'til it's pretty"? People well studied in the art of T-SQL and logical thinking know how to do all 3 without missing a beat.

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

Manie Verster (2/22/2010)
I checked out Grant Fritchey's[/url] article and was, like Jeff, not very pleased to see some while loops but one query attracted my attention and I tested it and I like it.
;WITH Nbrs(n) AS
(
SELECT 1
UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 100
)
SELECT CASE WHEN n%5=0 AND n%3=0 THEN 'BizzBuzz'
WHEN n%3 = 0 THEN 'Bizz'
WHEN n%5 = 0 THEN 'Buzz'
ELSE CAST(n AS VARCHAR(8))
END
FROM Nbrs
OPTION (MAXRECURSION 100);

Nice go, Manny... but because I'm mostly against the use of procedural code, I'd have to deduct a lot of points on this one. Contrary to popular belief, recursive CTEs are [font="Arial Black"]NOT[/font] set based and they are frequently slower and more resource intensive than a well written WHILE loop.

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

Gary Varga (2/22/2010)
Richard is absolutely correct.
Although I'd like to see a tribunal oversee a heated debate on coding standards - I'd be killing myself!!!
Seriously though, it is an important issue that can no longer be talked about above board. As with any kind of censorship, it does not eradicate it, it just moves underground.....allegedly

I'd prefer not to invoke "coding standards" on anyone but it always seems to be necessary. What I'd really like is for everyone to understand the value of proper embedded documentation, code readability, and consistency of code. The problem is that instead of letting people who are supposed to know their job well do what is right, people take too many shortcuts because they think embedded documentation, readability, and consistency all take too long and then you have to hit them with the "Standards" pork chop.

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 5

reidres (2/22/2010)
Manie Verster,
I notice that you used a temporary table rather than a table variable. Why did you make that choice?
Thank you.

There shouldn't be a Temp table or Table variable associated with this problem.

--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)

Jerry Day SSC-Addicted Points: 454 More actions · Answer 6

WITH bozo (i) as

(

select 1

union all select i+1 from bozo where i < 100

)

SELECT CASE when i % 3 = 0 and i % 5 = 0 then 'Fizzbuzz' when i % 3 = 0 THEN 'Fizz' when i % 5 = 0 THEN 'Buzz' else cast( i as char (4) ) END

FROM bozo

Jerry D

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 7

For all of those who are using a WHILE loop or Recursive CTE to perform the simple act of counting, please see the following article for a way to count using a couple of high speed, set based methods...

... Like it says in my signature line below, stop thinking in rows... think in columns.

http://www.sqlservercentral.com/articles/T-SQL/62867/

I would also take points off for anyone that made it so the problem couldn't be changed or couldn't be scaled up simply by changing the value of a variable or two. Think "production code" especially on interviews. There's a lot of competition out there and I'll be running everyone's solution through profiler at a million rows to see just how bad their code is. If you write a loop for this simple problem, I can almost guarantee you that the person who writes a set based solution will get the job and not you.

--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)

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 8

Jerry Day (2/22/2010)
WITH bozo (i) as
(
select 1
union all select i+1 from bozo where i < 100
)
SELECT CASE when i % 3 = 0 and i % 5 = 0 then 'Fizzbuzz' when i % 3 = 0 THEN 'Fizz' when i % 5 = 0 THEN 'Buzz' else cast( i as char (4) ) END
FROM bozo

Again... Recursive CTEs can be and frequently are worse for performance and resource usage. Recursive CTEs are NOT set based.

I'd also have to take off some serious points for formatting, readability, consistency, and scalability.

--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)

Loner SSC-Insane Points: 21279 More actions · Answer 9

Loner

SSC-Insane

Points: 21279

February 22, 2010 at 6:37 am

#1122898

Jeff

Why don't you post your 'perfect' answer?

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 10

Loner (2/22/2010)
Jeff
Why don't you post your 'perfect' answer?

I can tell you why... people would simply memorize instead of taking the time to figure it out. 😉

--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)

Jerry Day SSC-Addicted Points: 454 More actions · Answer 11

Jeff, with all due respect, I raced against the clock (5 min, 12 seconds) to complete this.

The crux of the challenge was to code a successful solution in the shortest amount of time.

Jerry D

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 12

Jerry Day (2/22/2010)
Jeff, with all due respect, I raced against the clock (5 min, 12 seconds) to complete this.
The crux of the challenge was to code a successful solution in the shortest amount of time.

With the same respect, if you write code like that just because there's a little pressure on you, then you're probably not the candidate I'd be seeking. 😉

--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)

OCTom SSChampion Points: 11785 More actions · Answer 13

This is cute but useless. If you're testing candidates who can't even solve this problem fairly quickly, your hiring practices need work. You should be weeding these people out before the first interview.

I was once asked to write a fairly easy program that was actually useful to the organization. I was given two hours to do it. It was a good test and when you were done, there was a useful product at the end.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 14

LOL, in an interview, I'd think the CTE, or a short WHILE loop would make sense, having the person talk through what they're doing. This isn't a test of great programming ability, it's a weed out of lower programming ability. Or a lack thereof.

I've been surprised how many people couldn't find duplicates in a short table. They either keep pestering me with questions on other columns, indexes, keys, etc., or they write a count() code without a HAVING. You might also be surprised how many people still return @@identity. BTW, does anyone use that?

Martin Vrieze SSCrazy Points: 2760 More actions · Answer 15

Guess I'm just not a good programmer.

Sorry Steve I don't measure up.:crying: