FizzBuzz

  • Jeff Moden

    SSC Guru

    Points: 994667

    BTW... the real reason why I'm asking the above question is that it may explain why so many people have questions like "I deleted a table and only have a backup from yesterday... how do I get today's data back?"

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

  • TomThomson

    SSC Guru

    Points: 104772

    Jeff Moden (4/19/2010)


    I didn't mean for it to sound harsh on my part. Let me ask though simply because I lead a life sheltered by large batch jobs where only staging tables are in a DB with the SIMPLE recovery mode... would you say that most production databases that folks build are in the SIMPLE recovery mode or BULK INSERT/ FULL recovery mode?

    It didn't sound harsh at all Jeff, just as if you had been sheltered from some of the rather nasty things that exist out there in the wild (eg CISCO used SQL Server Desktop Engine using simple recovery model in their BBSM product, which was typically installed in situations where the database was updated throughout the day and night so that without full recovery model data loss was guaranteed any time the server went down other than in a tidy controlled shutdown) and from the practise of using small databases to describe things like availability of features, menu structures, and customisations in other products delivered to many people (those small databases can be replaced by new releases, but other than that they are read only so there's no use to full recovery model).

    I think that if someone builds their own production databases they will usually want full recovery model - I certainly don't disagree with you on that one - although history databases (see below) are an exception. Even the small read-only databases mentioned above have master copies at the product suppliers site which are writable (how else do new versions get generated) and these copies may well use full recovery model. And of course the "nasty" cases ought to have had full recovery model even though they actually don't. So maybe I overstated my disagreement with your statement.

    I suspect that if we both were looking at the same database and the same use of it we would agree on which recovery model to use. All my writeable production databases, except some history databases (see below), have had full recovery model. It's just that I've come across a large number of DBs that don't have full recovery model, some for good reasons and some for bad reasons, and I read you as suggesting that databases without full recovery model would be very rare.

    History databases (I don't want to call them DW, because the data isn't massaged in any way, it's just a direct copy of information from the source databases) which are designed to be able to use simple recovery model are quite rare, mainly because people want to do one or more of (a) massage the source data (b) handle data for which it's hard to see what has and what hasn't already been imported (c) do computation and store precalculated partial results of MIS queries in the history DB instead of in a separate MIS DB. In many systems (b) is an inevitable consequence of the requirements (or, sometimes, of the fact that the source databases weren't designed with row-versioning in mind, or that the source databases aren't allowed to talk to each-other). In others (a) is needed to get the cost of the MIS queries down to an acceptable level. Once (a) or (b) is happening, there's little point in keeping the two DBs separate (but I try to educate people who want to merge the two databases when neither (a) nor (b) applies, since having the history database designed to use simple recovery can save an awful lot of disc space and backup store).

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    Jeff Moden (4/19/2010)


    BTW... the real reason why I'm asking the above question is that it may explain why so many people have questions like "I deleted a table and only have a backup from yesterday... how do I get today's data back?"

    All you can tell those people is to try to get hold of an executable for DBCC TimeWarp. Or you could put it less politely than that. Or maybe more politely if it's one of your VPs.

    Anyone who is using simple recovery model for a DB which is updated continuously in a way that is not automatically repeated if the DB reverts to an earlier state is either an idioy or using a product supplied by an idiot.

    Anyone who does a big update to a DB using simple recovery model with changing the recovery model first and taking a backup after the update before changing the recovery model back to simple (or by some other means ensuring that the data can be recovered without too much pain if there's a failure before the next scheduled backup) is hopelessly careless.

    Tom

  • Andrew Gothard-467944

    Hall of Fame

    Points: 3908

    richard-674310 (2/22/2010)


    Hey Steve,

    I'm interested in your statement that team fit is important. I happen to agree with you.

    You might want to consult your HR department on this - if you have one. I think it may be illegal (in the UK) to discriminate on NON-OBJECTIVE grounds. Ie. "X scored lower than Y on the test" is an OBJECTIVE reason to not hire X. I suspect that "I could never work with him/her" is illegal (in the UK).

    If you don't hire X, it is possible you might have to defend your reasons in front of an employment tribunal.

    Not at all. In some workplaces, particularly those which are publicly funded such as the NHS, there are strict guidelines in place to try and ensure objectivity in selection at least to the point of interview. However, there are certainly no legislative measures to prevent the rejection of a candidate because they would not fit into the team. In fact, I've been rejected on this basis myself (much to the bemusement of my mates - although I suspect one's friends may be biased on this point, more or less by definition).

    Of course, this may cover a multitude of sins which are covered - would they "not fit into the team" because they're black and half the team are BNP supporrters, or because they're older than the rest of the team and they'd feel more comfortable with someone younger, or because they're female - and the team wouldn't want to work with a girl in a tech position?

  • chillw1nston

    SSC Enthusiast

    Points: 147

    window function version

    SELECT TOP 100 --*

    --,

    CASE WHEN ROW_NUMBER() OVER (ORDER by object_id) % 3 = 0 THEN 'FIZZ'

    WHEN ROW_NUMBER() OVER (ORDER by object_id) % 5 = 0 THEN 'BUZZ'

    ELSE CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER by object_id))

    END AS FIZZBUZZ

    FROM sys.columns

  • Luis Cazares

    SSC Guru

    Points: 183568

    chillw1nston (1/27/2014)


    window function version

    SELECT TOP 100 --*

    --,

    CASE WHEN ROW_NUMBER() OVER (ORDER by object_id) % 3 = 0 THEN 'FIZZ'

    WHEN ROW_NUMBER() OVER (ORDER by object_id) % 5 = 0 THEN 'BUZZ'

    ELSE CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER by object_id))

    END AS FIZZBUZZ

    FROM sys.columns

    It's great to see someone new trying to contribute to the site. Thank you for that.

    You might want to note that there's an extensive discussion on this thread to get the best solution. Your solution has a problem as it won't show 'FizzBuzz' for multiples of 3 and 5 and it won't scale beyond the number of rows of your sys.columns table. Read the thread and you might find out a few good tips.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Jeff Moden

    SSC Guru

    Points: 994667

    Shifting gears a bit, in the last 20 interviews that I've conducted, I ask the candidates to wirte code to create and populate a table with all integer values from 1 to 1,000,000. Two have done the job with While loops. The other 18 couldn't do it. Of course, a lot of those people with "extensive experience in SQL Server/T-SQL" couldn't even tell me how to get the current date and time using T-SQL. Only 2 of the 20 DBA and Dev candidates could tell me the differences between Clustered and Non-Clustered Indexes even though most of them also had "extensive store procedure tuning experience" on their resume.

    I've only gotten past the easy questions with a couple of the candidates. I'm loading the triple banded Pork Chop Launcher for the next discussion with some of the recruiters we've been trying to work with.

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

  • Lynn Pettis

    SSC Guru

    Points: 442143

    Jeff Moden (1/27/2014)


    Shifting gears a bit, in the last 20 interviews that I've conducted, I ask the candidates to wirte code to create and populate a table with all integer values from 1 to 1,000,000. Two have done the job with While loops. The other 18 couldn't do it. Of course, a lot of those people with "extensive experience in SQL Server/T-SQL" couldn't even tell me how to get the current date and time using T-SQL. Only 2 of the 20 DBA and Dev candidates could tell me the differences between Clustered and Non-Clustered Indexes even though most of them also had "extensive store procedure tuning experience" on their resume.

    I've only gotten past the easy questions with a couple of the candidates. I'm loading the triple banded Pork Chop Launcher for the next discussion with some of the recruiters we've been trying to work with.

    I would not want to be the recruiters you have been working with.

  • mister.magoo

    SSC-Forever

    Points: 47068

    So you didn't get them to explain the rules for a successful quirky update then I guess?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Jeff Moden

    SSC Guru

    Points: 994667

    mister.magoo (1/27/2014)


    So you didn't get them to explain the rules for a successful quirky update then I guess?

    😀

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

  • chillw1nston

    SSC Enthusiast

    Points: 147

    --Now scalable, set based and added a fizzbuzz!

    DECLARE @threshold int = 100

    ;WITH numbers ( seqnum )

    AS ( SELECT 1 AS seqnum

    UNION ALL

    SELECT seqnum + 1 AS seqnum

    FROM numbers

    WHERE seqnum < @threshold

    )

    SELECT

    CASE WHEN ROW_NUMBER() OVER (ORDER by seqnum) % 3 = 0 AND ROW_NUMBER() OVER (ORDER by seqnum) % 5 = 0 THEN 'FIZZBUZZ'

    WHEN ROW_NUMBER() OVER (ORDER by seqnum) % 3 = 0 THEN 'FIZZ'

    WHEN ROW_NUMBER() OVER (ORDER by seqnum) % 5 = 0 THEN 'BUZZ'

    ELSE CONVERT(VARCHAR,ROW_NUMBER() OVER (ORDER by seqnum))

    END AS FIZZBUZZ

    FROM numbers

  • Gail Shaw

    SSC Guru

    Points: 1004446

    chillw1nston (1/28/2014)


    --Now scalable, set based and added a fizzbuzz!

    DECLARE @threshold int = 100

    ;WITH numbers ( seqnum )

    AS ( SELECT 1 AS seqnum

    UNION ALL

    SELECT seqnum + 1 AS seqnum

    FROM numbers

    WHERE seqnum < @threshold

    )

    Recursive CTEs are questionably set-based (will leave it to you to figure out why) and slower than many other methods of row generation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • chillw1nston

    SSC Enthusiast

    Points: 147

    because they cannot be run in parallel guess?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    No.

    How does a recursive CTE work?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • chillw1nston

    SSC Enthusiast

    Points: 147

    not sure what your getting at. I guess because it runs recursively and relys on previous result set to generate next which is a more programmatical construct?

    Would you advocate a cross join approach using system tables instead? Thats all i can think of without looping

  • Viewing 15 posts - 346 through 360 (of 364 total)

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