FizzBuzz

  • Gary Varga

    SSC Guru

    Points: 82166

    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: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jerry Day

    Old Hand

    Points: 374

    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: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Loner

    SSC-Insane

    Points: 21279

    Jeff

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

  • Jeff Moden

    SSC Guru

    Points: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jerry Day

    Old Hand

    Points: 374

    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: 994239

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • OCTom

    SSChampion

    Points: 11755

    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: 715064

    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

    Guess I'm just not a good programmer.

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

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

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