SELECT

Question

Post reply

SELECT

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

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

kyliedude SSC-Addicted Points: 492 More actions · Answer 1

...and if you check out the question statistics - that's what most people are doing - getting it, erm, 'right'.

kyliedude SSC-Addicted Points: 492 More actions · Answer 2

Whoa there Master Hugo - where did that lecture come from? You're just doing this to get extra points really... 😀

Henrik Jensen-489138 SSC Enthusiast Points: 123 More actions · Answer 3

Never mind the point, but the answer should be correct (2,0).

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

kyliedude (10/5/2012)
where did that lecture come from?

It wasn't intended as a lecture. It was just me letting off steam after seeing too many messages (not only in this topic, many topics have the same issue) of people "demanding" "their" points. I simply don't get why those points matter to people.

It was not my intention to single you out, or to offend you. My apologies if you did feel offended.

I do still hope that people will stop and consider exactly how important those points really are, and if that warrants the tone of some of the messages I am seeing.

(Hmm, so maybe it was a lecture after all.. ;))

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

🙁

Question: How many rows are returned from the selects listed above?

Explanation: You cannot insert null values into a not nullable column.

Everyone knows that.

So what is the gain from this question? Even trivial one, I still do not have answer how many rows should the two selects return.

Igor Micev,My blog: www.igormicev.com

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 6

A deceptively simple question, +1 for me - thanks. Perhaps slightly better wording next time though please. 2,0 is also, technically, correct according to the terms of your question.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 7

Another confusing QotD! The answer is definitely 2,0. That is the results from the "selects". The "insert" errors out. It is very straight-forward.

koragar SSC-Addicted Points: 407 More actions · Answer 8

The question should have been either how many rows are inserted which would match with the 'right' answer or 2,0 should be the correct answer.

Rich Weissler Hall of Fame Points: 3286 More actions · Answer 9

Add one more to the list of people who answered 2, 0 for the reasons given.

I was just tickled that I knew the really correct answer this time, knew that it would also throw an error and why, and that there would be more than a little discussion over in the forum about it. 🙂 All before I submitted my answer.

I'd be doggone if dropping by every day isn't teaching me something. Thanks folks. 😉

Rc.Rodrigues SSC Rookie Points: 44 More actions · Answer 10

Sorry, but there question is about the Select's. The error happened on Insert and not in SELECT, so the correct answer would be 2.0.

sestell1 SSChampion Points: 10230 More actions · Answer 11

Hugo Kornelis (10/5/2012)
I *knew* when I saw the answer options that there would be a possible source of confusion here.
Yes, the insert into the second table will fail. But that insert is not batch-aborting. The second select will still run, and because the insert has failed, it will return an empty set.
The answer is: "How many rows are returned from the selects listed above?" (emphasis mine). The only correct answer is 2,0, since the number of rows returned by the second row is not equal to "error" (which is not even a number), but "zero".

I agree. Based on the wording of the question, the answer really should be "2,0". Both select statements run without error.

sknox SSChampion Points: 12388 More actions · Answer 12

Hugo Kornelis (10/5/2012)
I *knew* when I saw the answer options that there would be a possible source of confusion here.

Yes, me too. So to make sure whether the second select would run, I actually ran the code.

Yes, the insert into the second table will fail. But that insert is not batch-aborting. The second select will still run, and because the insert has failed, it will return an empty set.

Which my test verified.

The answer is: "How many rows are returned from the selects listed above?" (emphasis mine). The only correct answer is 2,0, since the number of rows returned by the second row is not equal to "error" (which is not even a number), but "zero".

Agreed. Furthermore, to be pedantic, if the SELECT statement never ran, it would still have returned 0 rows, so the correct answer would still be 2,0.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

demonfox (10/4/2012)
Lokesh Vij (10/4/2012)
sam.dahl (10/4/2012)
I recognised the attempt to insert null and hence picked 2,0 because that's what the two select statements return: 2 and 0. It's the insert statement that fails.
+1
I also misunderstood the question.
well , I got it right .. I guess ; I didn't consider the select statement returning nothing..
But I would say , 2,0 should be the valid answer , since that is actually being asked in the question..
+1

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mike Hays SSCommitted Points: 1871 More actions · Answer 14

Just restating what has already been discussed: the error was on the insert, not the select statement. If you run the same select statement ten more time, you will not get ten more errors.

The question did make me think about not nulls, but the wording in the question did ask for the "SELECTS".

sqlserver8650 Ten Centuries Points: 1121 More actions · Answer 15

Good Question,

I am thinking in different way in terms of table variable even I did not look at not null constraint