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

Nulls Expand / Collapse
Author
Message
Posted Wednesday, December 08, 2010 4:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:26 AM
Points: 1,658, Visits: 6,002
da-zero (12/8/2010)
Some people are still working with earlier versions of SQL Server.


But hopefully none of them are still using ANSI_NULLS OFF or any other deprecated features?
Post #1031782
Posted Wednesday, December 08, 2010 5:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:47 PM
Points: 1,298, Visits: 1,306
Toreador (12/8/2010)
da-zero (12/8/2010)
Some people are still working with earlier versions of SQL Server.


But hopefully none of them are still using ANSI_NULLS OFF or any other deprecated features?

Some old applications are using ANSI_NULLS OFF.

Try to explain to users that filters of the type name not like 'honza%' don't return those lines where name is empty. They don't start with honza...




See, understand, learn, try, use efficient
© Dr.Plch
Post #1031794
Posted Wednesday, December 08, 2010 5:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
Caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results

This is not in the local BOL of my work environment. And I have no control over that.

I used to try to answer QOTD based on my knowledge and research in BOL.
But questions like this are the reason I now run the code first and that takes the fun out of it.
Post #1031811
Posted Wednesday, December 08, 2010 5:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:09 AM
Points: 1,337, Visits: 746
Nice question!
Post #1031816
Posted Wednesday, December 08, 2010 6:05 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
steven.malone (12/8/2010)
Caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results

This is not in the local BOL of my work environment. And I have no control over that.

I used to try to answer QOTD based on my knowledge and research in BOL.
But questions like this are the reason I now run the code first and that takes the fun out of it.


This logic i dont get at all. The question is great for showing a "gotcha" in SQL. If it isnt in your BOL... then even better. You just learned something that you dont know and couldnt have found out any other way (since its not in your BOL). The QOTD isnt there for us to score points... its there to teach/spread knowledge. Sometimes its not so good... but then the discussion is normally better. So either way... goal accieved. The points are just a bonus... if you get it right.

Seriously whats the point of running the code... getting your point and then moving along?

/T
Post #1031825
Posted Wednesday, December 08, 2010 6:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:47 PM
Points: 1,298, Visits: 1,306
tommyh (12/8/2010)
The QOTD isnt there for us to score points...

It's easy to say this if you have one thousand points




See, understand, learn, try, use efficient
© Dr.Plch
Post #1031831
Posted Wednesday, December 08, 2010 6:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:58 AM
Points: 819, Visits: 832
tommyh (12/8/2010)
The QOTD isnt there for us to score points... its there to teach/spread knowledge.

Exactly. I knew I was answering the question incorrectly -- it was the result I would have expected, which is never the right answer in QotDs -- but I went with that answer anyway because I wanted to learn something. I have no idea how many "points" I have, nor do I care, nor do I know what I would do with that information if I had it.

[edit: Okay, now I see how many points I have. And then what, can I trade them in for prizes like skee-ball tickets or something?]

Ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1031833
Posted Wednesday, December 08, 2010 6:25 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
honza.mf (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points...

It's easy to say this if you have one thousand points


Those points also go up from posting. So in theory i could have answered ALL QOTD wrong and have 1000... 1001 forum posts... so doesnt say that much.

/T
Post #1031835
Posted Wednesday, December 08, 2010 6:27 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
ronmoses (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points... its there to teach/spread knowledge.

Exactly. I knew I was answering the question incorrectly -- it was the result I would have expected, which is never the right answer in QotDs -- but I went with that answer anyway because I wanted to learn something. I have no idea how many "points" I have, nor do I care, nor do I know what I would do with that information if I had it.

[edit: Okay, now I see how many points I have. And then what, can I trade them in for prizes like skee-ball tickets or something?]

Ron


No you can only compare it to others like Jeff Moden and GilaMonster... and cry

/T
Post #1031837
Posted Wednesday, December 08, 2010 6:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:47 PM
Points: 1,298, Visits: 1,306
tommyh (12/8/2010)
honza.mf (12/8/2010)
tommyh (12/8/2010)
The QOTD isnt there for us to score points...

It's easy to say this if you have one thousand points


Those points also go up from posting. So in theory i could have answered ALL QOTD wrong and have 1000... 1001 forum posts... so doesnt say that much.

/T

Yessir. And I now obtain one more point!
Sorry, I have a bad day (or maybe a week) and this to relax a little.




See, understand, learn, try, use efficient
© Dr.Plch
Post #1031848
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse