SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / SQL 2008 T-SQL / Latest Posts

RE: SQL 2008 T-SQL

L' Eomot Inversé — Fri, 19 Mar 2010 21:59:30 GMT

A nice question. I was lucky enough to see it after the wording was changed to reduce the number of correct answers to one. But I do feel that this question illustrates how nasty MERGE is - we were better off without it, at least from the point of view of code clarity.

RE: SQL 2008 T-SQL

Bhavesh_Patel — Fri, 20 Nov 2009 22:50:25 GMT

I was lucky :hehe: I got it right!

RE: SQL 2008 T-SQL

VM-723206 — Tue, 01 Sep 2009 09:09:15 GMT

Good question!

RE: SQL 2008 T-SQL

bitbucket-25253 — Sat, 22 Aug 2009 07:28:41 GMT

[quote][quote]Jamie Longstreet (8/21/2009)--------------------------------------------------------------------------------Excellent question. First because it demonstrates the MERGE, Second because it illustrates the $action,Nice one![/quote][quote]dave.farmer Actually, I was going to say much the same but got a bit sidetracked by the answer confusion... I didn't know about MERGE before but thanks to this QotD it's clear how it works and it will be most helpful in solving a problem I have at work without resorting to extra and less efficient processing.Cheers![/quote][/quote]Thank you for your responses, it is satisfying to recieve affirmation that the QOD has achieved at least a portion of its objective.

RE: SQL 2008 T-SQL

dave.farmer — Fri, 21 Aug 2009 15:29:59 GMT

[quote][b]Jamie Longstreet (8/21/2009)[/b][hr]Excellent question. First because it demonstrates the MERGE, Second because it illustrates the $action,Nice one![/quote]Actually, I was going to say much the same but got a bit sidetracked by the answer confusion... I didn't know about MERGE before but thanks to this QotD it's clear how it works and it will be most helpful in solving a problem I have at work without resorting to extra and less efficient processing.Cheers!

RE: SQL 2008 T-SQL

Jamie Longstreet-481950 — Fri, 21 Aug 2009 14:28:20 GMT

Excellent question. First because it demonstrates the MERGE, Second because it illustrates the $action, and Last because the format for the output makes the answer particularly clear.Specifically:INSERT 6 Production Jones NULL NULL NULLUPDATE 2 Sales Erickson 2 Sales ByhamDELETE NULL NULL NULL 5 Manufacturing BrewerNice one!

RE: SQL 2008 T-SQL

paul.goldstraw — Fri, 21 Aug 2009 09:39:04 GMT

I'm not trying to be difficult here but I have to disagree with you bitbucket.If I write an update statement i get a response along the lines of [code="sql"]23 row(s) affected[/code]Your correct answer implies that what we should expect is [code="sql"]23 row(s) affected and 34 row(s) unaffected[/code]which isn't the case. If a row is untouched, as row 1 is, the fact it was untouched is implicit and needs no explanation as such. I appreciate it was an error that has now been corrected but trying to defend it by suggesting we missed a subtlty of the question rather than admitting that it was wrong is missing the objective of the QotW as much as you suggest we are.Paul

RE: SQL 2008 T-SQL

bitbucket-25253 — Fri, 21 Aug 2009 09:17:24 GMT

Composed before I read Steve Jones post .... Sorry about that Steve I should have hit the refresh before hitting post.[quote]Scott ColemanHow on earth can you claim "no changes" is an action that was executed?[/quote][quote]Cliff Jones Yes, please explain the difference between answers 1 and 2 because it is very subtle[/quote]The action that was executed by the merge statement was [b]the comparison between all the data in the table depts and all the data in table delta[/b] to determine if additional further action was required. The objective of the QOD as I understand it is to get people thinking and learning. And the subtle actions of the merge is that all the rows in both tables are examined.[quote]Scott ColemanI'm really getting tired of questions that play word games[/quote]Scott Coleman - how about you submitting a QOD. It is quite a challenge. Edited at 11:20 AM

RE: SQL 2008 T-SQL

Longy — Fri, 21 Aug 2009 09:14:00 GMT

I read the first answer, knew it was right and ignored the rest. I don't see how doing nothing is an action

RE: SQL 2008 T-SQL

Steve Jones - SSC Editor — Fri, 21 Aug 2009 09:12:56 GMT

I missed that subtlety with 1 and 2. I've changed the answers to clean things up and will award back points.

RE: SQL 2008 T-SQL

Scott Coleman — Fri, 21 Aug 2009 08:32:55 GMT

How on earth can you claim "no changes" is an action that was executed?I'm really getting tired of questions that play word games.

RE: SQL 2008 T-SQL

mtassin — Fri, 21 Aug 2009 08:30:22 GMT

I agree... I got this one right, but I was torn between the equivacy of answer 1 and 2.1 makes no mention of row 1, hence it is left alone.2 distinctly states that nothing will happen to row 1... which equates to answer 1.

RE: SQL 2008 T-SQL

Cliff Jones — Fri, 21 Aug 2009 08:05:34 GMT

Yes, please explain the difference between answers 1 and 2 because it is very subtle.

RE: SQL 2008 T-SQL

Aluminum — Fri, 21 Aug 2009 07:25:07 GMT

I just guessed. This one was way to much to think about.

RE: SQL 2008 T-SQL

David Betteridge — Fri, 21 Aug 2009 06:51:24 GMT

I think answer 1 is correct as well!David

RE: SQL 2008 T-SQL

dave.farmer — Fri, 21 Aug 2009 06:50:37 GMT

Wrong here too - I spent a lot of time deciding between answer 1 and 2, and as Paul mentioned above, plumped for 1 as the question asked about actions and no action was taken with row 1 so it shouldn't be mentioned.Of course I knew I'd be wrong. I seem to have the unenviable knack of choosing the wrong one whenever there are multiple correct answers, and every time I do, I vow to give up on these questions - and by the next day I'm back, getting the wrong right answer yet again! :-{

RE: SQL 2008 T-SQL

kramaswamy — Fri, 21 Aug 2009 05:22:13 GMT

yeah, lucked out on getting it right, since both of those answers are technically correct.

RE: SQL 2008 T-SQL

paul.goldstraw — Fri, 21 Aug 2009 04:41:09 GMT

And I was the unlucky one who guessed the other way and got it wrong. Answers 1 and 2 are the same, but imo answer 1 is more correct, since the question asked[quote]What actions were executed in/on the rows in the Depts table?[/quote]Since no actions were executed on row 1 it should have been omitted.

RE: SQL 2008 T-SQL

Hugo Kornelis — Fri, 21 Aug 2009 03:51:12 GMT

I got my points, due to a lucky 50/50 guess. But I'd still like to know, what eexactly is the difference between [quote]DeptId 2 data was updated,Deptid 5 row was deleted,A new Deptid of 6 was added [/quote] and [quote]Deptid 1 no changes,DeptId 2 data was updated,Deptid 5 was deleted,A new Deptid of 6 was added[/quote]?

SQL 2008 T-SQL

bitbucket-25253 — Thu, 20 Aug 2009 23:26:29 GMT

Comments posted to this topic are about the item [B]SQL 2008 T-SQL[/B]