FizzBuzz

Question

FizzBuzz

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: 1004356 More actions · Answer 1

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.

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)

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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: 104773 More actions · Answer 3

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: 3928 More actions · Answer 4

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 More actions · Answer 5

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: 183706 More actions · Answer 6

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: 1004356 More actions · Answer 7

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.

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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: 47174 More actions · Answer 9

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

MM

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

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

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.

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)

chillw1nston SSC Enthusiast Points: 147 More actions · Answer 11

--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: 1004504 More actions · Answer 12

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 More actions · Answer 13

chillw1nston

SSC Enthusiast

Points: 147

January 28, 2014 at 7:11 am

#1683903

because they cannot be run in parallel guess?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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 More actions · Answer 15

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