Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

NULL Values and Joins Expand / Collapse
Author
Message
Posted Tuesday, December 21, 2010 8:53 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 8,844, Visits: 9,406
Seth Kramer (12/18/2010)
I accept I got it wrong, but what do you mean the second transaction fails? Only the insert null into the primary key field statement fails. There are no failed inserts on Table2.

It looks to me as if the explanation paragraph following the reference to BoL has got sucked in from some other explanation, as it certainly has no connection with this question. Also, the wording of the possible answers is horrible and could easily have been fixed by changing "where" to "last" and sticking a comma in front of it.
At first I thought I would have to rack my brains and remember how nulls are treated by ORDER BY, but then I noticed that there were only two options with 7 rows and neither of them had NULL as the last d value, so the ordering of nulls was irrelevant after all and it was actually a no-brainer question.


Tom
Post #1037787
Posted Wednesday, December 22, 2010 1:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
I got to wrong by misunderstanding the answer.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1038107
Posted Wednesday, December 22, 2010 3:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 2, 2011 11:50 PM
Points: 5, Visits: 7
Bradley Deem (12/20/2010)
Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.

BEGIN TRAN
INSERT INTO [Table1]( a,b)
VALUES(1 ,'one')

INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a]
VALUES(NULL ,'five')
-- Even though the above insert STATEMENT fails, execution of the BATCH continues.
-- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing)
-- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO

INSERT INTO [Table1]( a,b)
VALUES(4 ,'join1')

INSERT INTO [Table1]( a,b])
VALUES(2 ,'join3')
COMMIT TRAN
GO


That's a good explanation, thank you. I'm usually a bit lazy with my error handling and use of transactions, hence my confusion.
Post #1038152
Posted Thursday, December 23, 2010 12:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 3:11 AM
Points: 9, Visits: 71
first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.

Correct answer: 7 rows where column d = three

can any body explain how it is possible?



thanks
Post #1038624
Posted Thursday, December 23, 2010 12:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 3:11 AM
Points: 9, Visits: 71
same result i got
Post #1038627
Posted Thursday, December 23, 2010 1:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:53 AM
Points: 2,529, Visits: 2,402
volter_ddun-856145 (12/23/2010)
first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.

Correct answer: 7 rows where column d = three

can any body explain how it is possible?



thanks

Please, read the last post of the first page.
Post #1038634
Posted Thursday, December 23, 2010 3:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:36 PM
Points: 6,002, Visits: 8,267
This was an ... interesting question.

I don't call it a good question, because of the complexity - not the complexity of the subject matter, but that of the number of statements, and the number of rows. We had to deal with behaviour of a failed insert in a transaction, then the behaviour of two kinds of joins, union, ordering, and the position of NULL values in an order by (which, for the record, is not the same in all RDBMS's; whether NULLs go first or last is not defined in the ANSI standard, but left as an implementation-dependant choice). And all that on tables with three or four rows.

I think a good QotD should test one, maybe two subjects only. And preferably with a combination of statement complexity and number of rows that enables those with an understanding of the subject to work out the results in their head. This one severely pushed my ability to work it out in my head.

SanDroid, I'd love to see more questions from you, as the idea for this question is great - but as a suggestion for the next time, consider using two or three rows in the tables, not more. And don't add extra complexity by adding weird primary keys, failed inserts, etc. Just giving the table population, a query with a join on nullable columns, and some believable but incorrect answer options would have been enough.

As to the erroneous explanation - well, you already explained that this is the result of changes to question and explanation getting out of sync. Stuff like that happens, 'nuff said.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1038678
Posted Sunday, December 26, 2010 5:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 10:45 AM
Points: 39, Visits: 28
:) don't worry, it happened to me too, I did not read carefully the question. Morale of the story: it is better to pay attention at the requirements and just not knowing the correct answer, than missing the question and answer the wrong thing. However, the explanation they provided, that 'no rows are inserted in the Table2' is completely bizarre. (repairing frozen pipes qualify for not reading carefully)
Post #1039290
Posted Tuesday, December 28, 2010 11:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
Thanks for the question, and the explanation of why the explanation seemed so weird.
Post #1039893
Posted Tuesday, January 4, 2011 12:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:06 AM
Points: 10, Visits: 3
why you give me that without beeing before responding
Post #1042189
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse