SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI_NULLS


ANSI_NULLS

Author
Message
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35702 Visits: 9518
Hugo Kornelis (4/8/2010)
RBarryYoung (4/7/2010)
Paul:

Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.

That was a great question indeed, as is this one from Paul. And though I got Paul's question right (bit of luck!), I'll admit that I was in the majority that replied wrong to RBarry's question.

But one of the lowest correct percentages that you know of? I don't think so!
http://www.sqlservercentral.com/questions/Design+and+Theory/64698/ - 37%
http://www.sqlservercentral.com/questions/SQL+Server+2008/63588/ - 35%
http://www.sqlservercentral.com/questions/Design+and+Theory/64304/ - 20%
http://www.sqlservercentral.com/questions/Administration/67278/ - 16%
http://www.sqlservercentral.com/questions/Administration/67277/ - 15%

And I've got two new questions lined up; they are scheduled for April 20th and May 6th. I don't think they're quite as hard as the last three from this list, though.

Ouch! Me too, except I only got the 35% one right. And I'm concerned :-Dthat the main thing that I may have learned from these was to seriously avoid IGNORE_DUP_KEY. :-D

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35702 Visits: 9518
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.

Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.

Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). :-D


:-DWhat do you want it to do?
Do you want it to
(a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or
(b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or
(c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or
(d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0)
??
For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL).


I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be non-asymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfinite-class number of ways that was at least one transfinite-class higher than that for "0/0". But that's probably too many exceptions to handle in most programs. :-D

Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... :-D

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67819 Visits: 18570
RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.

Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.

Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). :-D


:-DWhat do you want it to do?
Do you want it to
(a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or
(b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or
(c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or
(d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0)
??
For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL).


I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be non-asymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfinite-class number of ways that was at least one transfinite-class higher than that for "0/0". But that's probably too many exceptions to handle in most programs. :-D

Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... :-D


You raise another quite interesting topic for me. Is 0/0 = 1 or is 0/0 an error?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35702 Visits: 9518
CirquedeSQLeil (4/8/2010)
RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.

Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.

Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). :-D


:-DWhat do you want it to do?
Do you want it to
(a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or
(b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or
(c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or
(d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0)
??
For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL).


I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be non-asymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfinite-class number of ways that was at least one transfinite-class higher than that for "0/0". But that's probably too many exceptions to handle in most programs. :-D

Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... :-D


You raise another quite interesting topic for me. Is 0/0 = 1 or is 0/0 an error?

In arithmetic, it's just "undefined", technically a domain (input) exception, I think. In theoretical mathematics study of such things (Real/Complex Calculus, Real/Complex Analysis, etc.) it's a discontinuity, and a very interesting one at that (and Power(0,0) even more so), because unlike 1/0 you can usually manipulate whatever equation it is embedded in to determine what value it "should" have in that particular case.

(Note: and now I realize/remember that my previous reply to Tom only makes sense when the expression (0 to the 0th power) is a result returned by an equation...:w00tSmile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35702 Visits: 9518
RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010)
Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.

Ooo - I had never tried POWER. Have now, and you're right - though POWER (0, -1) gives me a divide-by-zero, rather than a domain error or invalid floating point operation.

Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). :-D


:-DWhat do you want it to do?
Do you want it to
(a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or
(b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or
(c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or
(d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0)
??
For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL).


I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be non-asymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfinite-class number of ways that was at least one transfinite-class higher than that for "0/0". But that's probably too many exceptions to handle in most programs. :-D

Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... :-D

Now I am remembering, that it only makes sense to talk about (0**0) when it's a result returned from an equation, and only in the context of that equation's form. IIRC, my professor was talking about the function:

Z = X^Y When X=0, and Y=0

It's really nasty...;-)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19003 Visits: 12426
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error?

To that question, my answer is a clear and resounding "yes"! :-P


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67819 Visits: 18570
Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error?

To that question, my answer is a clear and resounding "yes"! :-P


Glad you liked it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26249 Visits: 12506
RBarryYoung (4/8/2010)

Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... :-D

Not just you, me too:-D. I can understand (and do share) that prickliness. When some holier-than-thou purist starts ranting against NULLs (often at the same time as claiming to be a follower of Codd, which is just bloody Crazy ridiculous) and telling people that they shouldn't need a name for bridge tables (or MM tables or whatever you want to call them) because all tables are equal Sad I begin to see red, so I thought your comment let David Portas off far too lightly the other day.

I also find it a bit irritating that MS's SQL team don't bite the bullet and provide full support for the IEEE floating point standard; I know it isn't trivial (probably needs a NOT NaN constraint on a column which is to be used in an index, with errors caused for trying to put a NaN there just as there are for trying to put NULL in a column with a NOT NULL constraint) but it really would make that part of arithmetic a bit cleaner. Another good thing about it is that it would probably annoy the relational purists - effectively we'd be adding a domain-specific NULL, which might to the final end to any hope of getting a "pure" NULL-free relational model accepted. But MS's failure to provide that is a small thing compared to what the discontinuation of NULL would do.

Tom

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26249 Visits: 12506
Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error?

To that question, my answer is a clear and resounding "yes"! :-P

And mine is a resounding "sometimes"! Unless it's a resounding "perhaps"? :-D

Of course to see why you have to understand what Barry was getting at with his talk of equations (I tend to talk about functions in that context, but it's the same thing). (If you're not interested in the mathematics of functions over the real plain, read no further).

If you look at the function f(X,Y) = (2X/Y) and ask what its limit is as X and Y approach 0 you can see straight away that what the limit is depends on what path in the plane you follow to get to 0,0 (that's why in arithmetic 0/0 is undefined). If you start from the point X=1 Y=2 and follow a straight line to X=0 Y=0 the value everywhere along the line until you get to X=0 Y=0 the value is 1 everywhere along that line except at 0,0 so the limit at that point when you approach on that line is 1. So we can say that sometimes (for example when you walk down that line) 0/0 should probably be treated as 1. On the other hand, if you start from X=1 Y=0 and follow thre straight line to 0,0 every point produces an error; so if your on that line you had better treat 0/0 as an error. And if you start from X=1, Y=1 and follow a straight line to 0,0 the only value you see is 2. Of course it gets interesting if you don't follow a straight line: for example if you follow a spiral from somewhere to 0,0 you will see the value changing all the time, including very large negative values, very large positive values, everything in between, and errors - and although it changes in a nice regular pattern, there won't be (well, actually, this depends on the sort of spiral it is) a tendency towards some fixed value as you get closer to 0,0 so we can't say there's a limit value there on that path.

We [ex]mathematicians are very lazy, so if the limit was the same on every path we would just say the value is defined and is that limit; but as it's not we say the value is undefined except in the context of a particular function and a particular path.

Tom

RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35702 Visits: 9518
Tom.Thomson (4/8/2010)
Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error?

To that question, my answer is a clear and resounding "yes"! :-P

And mine is a resounding "sometimes"! Unless it's a resounding "perhaps"? :-D

Of course to see why you have to understand what Barry was getting at with his talk of equations (I tend to talk about functions in that context, but it's the same thing). (If you're not interested in the mathematics of functions over the real plain, read no further).

If you look at the function f(X,Y) = (2X/Y) and ask what its limit is as X and Y approach 0 you can see straight away that what the limit is depends on what path in the plane you follow to get to 0,0 (that's why in arithmetic 0/0 is undefined). If you start from the point X=1 Y=2 and follow a straight line to X=0 Y=0 the value everywhere along the line until you get to X=0 Y=0 the value is 1 everywhere along that line except at 0,0 so the limit at that point when you approach on that line is 1. So we can say that sometimes (for example when you walk down that line) 0/0 should probably be treated as 1. On the other hand, if you start from X=1 Y=0 and follow thre straight line to 0,0 every point produces an error; so if your on that line you had better treat 0/0 as an error. And if you start from X=1, Y=1 and follow a straight line to 0,0 the only value you see is 2. Of course it gets interesting if you don't follow a straight line: for example if you follow a spiral from somewhere to 0,0 you will see the value changing all the time, including very large negative values, very large positive values, everything in between, and errors - and although it changes in a nice regular pattern, there won't be (well, actually, this depends on the sort of spiral it is) a tendency towards some fixed value as you get closer to 0,0 so we can't say there's a limit value there on that path.

We [ex]mathematicians are very lazy, so if the limit was the same on every path we would just say the value is defined and is that limit; but as it's not we say the value is undefined except in the context of a particular function and a particular path.

Ouch! You nailed me there, I was actually too lazy to explain all of this ... :-P

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search