June 9, 2014 at 9:20 am
Andrew Kernodle (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software we use at my current company has NOLOCK all over the place, however it can manage it. INSERTs, UPDATEs, DELETEs... Who cares, throw NOLOCK on all the joins!
Oh, so as well as unreliable queries, they're happy about data corruption as well? http://support.microsoft.com/kb/2878968
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 9, 2014 at 9:30 am
andrew gothard (6/9/2014)
Andrew Kernodle (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software we use at my current company has NOLOCK all over the place, however it can manage it. INSERTs, UPDATEs, DELETEs... Who cares, throw NOLOCK on all the joins!
Oh, so as well as unreliable queries, they're happy about data corruption as well? http://support.microsoft.com/kb/2878968
In that case, nolock SHOULD NOT cause corrupted indexes, and I'm thinking thats why the patches are there. It would be interesting to know how a source table can cause corruption in a destination index! Rough looking bug!
June 9, 2014 at 9:48 am
andrew gothard (6/9/2014)
Andrew Kernodle (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software we use at my current company has NOLOCK all over the place, however it can manage it. INSERTs, UPDATEs, DELETEs... Who cares, throw NOLOCK on all the joins!
Oh, so as well as unreliable queries, they're happy about data corruption as well? http://support.microsoft.com/kb/2878968
Unreliable queries? Data corruption? The vendor's never seen either of those happen in their test environment (which, actually, they don't have), so it's not a problem! :-D.
Seriously, they can feign ignorance (or is it feigning? It could be genuine!) to any problem our company raises, and management just shrugs and accepts the idea that, when the vendor says they don't know what their coding is doing, we're pretty much stuck. It helps the vendor immeasurably that they're a corporation that a few government entities have purchased from, so they don't have to worry about losing customers. They just submit the lowest bid each year, which is still plenty sizable, and onward they go.
EDIT: Ack, and reading that KB article, it seems that problem was fixed in a CU for SQL Server 2008 R2 SP2. We're stuck on SP1 by the vendor's service agreement. And yes, it makes absolutely no sense for them to constrain the SP level we're on, especially since their coding uses no version-specific functionality besides TEXT-type columns, but that certainly doesn't slow them down 😛
- 😀
June 10, 2014 at 12:15 am
SQL is delicious (6/6/2014)
Koen Verbeeck (6/6/2014)
gbritton1 (6/6/2014)
Koen Verbeeck (6/6/2014)
TomThomson (6/6/2014)
Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore you could, in fact, divide a number by zero and that my calculator must be wrong when I proved to him that you couldn't. He wouldn't take my word for it because according to him, "Americans are bad at maths."
OK, he was an idiot. No-one claims that there are no British idiots.
Technically, you can divide a number by zero. In its asymptote it approaches infinity, so you can baldly say that dividing a number through zero equals infinity. Calculators are just dumb things that can't display infinity, can you believe that?
Technically? Not sure what that means. Division by zero is undefined in ordinary arithmetic and has no meaning.
Technically means looking at mathematical theory.
It is not undefined.
If you take y:= f(x) = 1 /x and you plot it out on a graph, you can clearly see that for x going to 0 y is going to infinity.
So you can say that in the asymptote of x->0, y equals infinity.
In the context of the conversation I was having with the colleague who told me it was possible (and appeared shocked that a calculator gave an "Err" response), I was pointing out to him that I saw potential for divide by zero errors in the functional spec that he wrote and was trying to make him aware that it would cause problems with the application. He denied that the application would error out under those circumstances.
In most programming languages it will definately throw an error and it should be.
In real-life applications, there is no room for infinity 😀
Some programming languages can return a "value" however, such as Inf or NaN (not a number). The R programming language is a good example.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 12:22 am
patrickmcginnis59 10839 (6/6/2014)
You're saying that in the function f(x) = 1/x, as x approaches 0, f(x) approaches infinity. If you draw this curve, you can be drawing forever and this curve will not intersect with the vertical line that represents 0, although it can get arbitrarily close. X can be infinitely small, but with this curve, there will always be a value in between x and 0. There really is no value with this curve that says that x is infinitely close to 0 at the same time that says there is no value possible that x > value > 0. The only time that its impossible to say x > ? > 0, is when x is zero, but this curve approaches zero on the axis and never meets it, although it can get infinitely close 🙂If I'm not mistaken, zero as a limit is still never zero by the very definition of "limit". Thats the impression I get when I google "divide by zero in calculus". But I'll admit that the given chances of me knowing more than you is also approaching zero but me being wrong rarely stops me from posting LOLOL
Infinity is an abstract concept. When you are drawing a line, you are still in the "finite realm" and you will of course keep on drawing forever, because that is the idea of infinity 😀
Getting really close but never reach it reminds me of one of the paradoxes of Zeno, which basically says that when you run towards a wall, you will never reach it because you always have to travel half the distance of the distance you just did. (1 + 1/2 + 1/4 + 1/8 + ...). In theory, you never reach the wall. In reality, you break your nose 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 12:31 am
gbritton1 (6/6/2014)
Koen Verbeeck (6/6/2014)
gbritton1 (6/6/2014)
Koen Verbeeck (6/6/2014)
TomThomson (6/6/2014)
Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore you could, in fact, divide a number by zero and that my calculator must be wrong when I proved to him that you couldn't. He wouldn't take my word for it because according to him, "Americans are bad at maths."
OK, he was an idiot. No-one claims that there are no British idiots.
Technically, you can divide a number by zero. In its asymptote it approaches infinity, so you can baldly say that dividing a number through zero equals infinity. Calculators are just dumb things that can't display infinity, can you believe that?
Technically? Not sure what that means. Division by zero is undefined in ordinary arithmetic and has no meaning.
Technically means looking at mathematical theory.
It is not undefined.
If you take y:= f(x) = 1 /x and you plot it out on a graph, you can clearly see that for x going to 0 y is going to infinity.
So you can say that in the asymptote of x->0, y equals infinity.
I have fair amount of University-level mathematics and understand theory reasonably well if not at an expert level. However, I was talking about ordinary arithmetic, not limits. In ordinary arithmetic, x/0 is most certainly undefined. If we're talking about limits, the best you can say is as x approaches 0, y approaches infinity. If x every reaches 0, the result is still undefined.
What? You are not by default talking about the Riemann sphere? 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 12:41 am
TomThomson (6/6/2014)
Wel, I reckon you can't. But the amusing thing is that model-theoretic models of various classes of numbers (natural numbers, integers, rationals, reals, complex numbers, etcetera ad nauseam) can easily be built to include infinite numbers (this is a consequence of the Lowenheim-Skolem theorem for predicate calculus) but in none of those models is divide by 0 possible.
There are certainly mathematical models where division by zero is defined, like for example the Riemann sphere or the extenden non-negative real number line. Most of the time it is infinity, which in itself is not an actual number (at least that is what my high schoo mathematics teacher hammered in).
TomThomson (6/6/2014)
Where your approach breaks down is that there's not one asymptote, there are two different ones. The nonexistent* limit as you approach 0 from below is a different infinity from the nonexistent limit as you approach zero from above. And you can't pretend they are both the same infinity, or all sorts of well understood proerties of convergence and limits break down, and things quickly lead to infinity = 0 and then to all numbers = 0. So you can't divide a number by zero if your maths has both positive and negative numbers.
That's true, and you can designate those two different asymptotes by assigning a sign to zero.
For example, lim (x->0-) and lim(x->0+). Which results in -Inf and +Inf respectively.
It's been a while (almost 15 years), but I remember using L'Hôpital's rule in high school to calculate the result of all those limits and their indeterminate forms (which in some cases, allows you to define a limit for 0/0).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 12:42 am
Eirikur Eiriksson (6/6/2014)
Koen Verbeeck (6/6/2014)
gbritton1 (6/6/2014)
Koen Verbeeck (6/6/2014)
TomThomson (6/6/2014)
Just a couple of examples: A British colleague who held an A-level in math and a bachelor's in business swore you could, in fact, divide a number by zero and that my calculator must be wrong when I proved to him that you couldn't. He wouldn't take my word for it because according to him, "Americans are bad at maths."
OK, he was an idiot. No-one claims that there are no British idiots.
Technically, you can divide a number by zero. In its asymptote it approaches infinity, so you can baldly say that dividing a number through zero equals infinity. Calculators are just dumb things that can't display infinity, can you believe that?
Technically? Not sure what that means. Division by zero is undefined in ordinary arithmetic and has no meaning.
Technically means looking at mathematical theory.
It is not undefined.
If you take y:= f(x) = 1 /x and you plot it out on a graph, you can clearly see that for x going to 0 y is going to infinity.
So you can say that in the asymptote of x->0, y equals infinity.
That would be the equivalent of a triangle where two corners are 90°
😎
Exactly. There is a mathematical proof (although I have forgotten everything about it) that says two parallel lines cross each other in infinity.
(which can be "similated" by looking at a railroad. They have parallel tracks, but at the horizon they seem to cross each other.)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 1:23 pm
Koen Verbeeck (6/10/2014)
What? You are not by default talking about the Riemann sphere? 😀
As neither SQL nor T-SQL supoprts a complex number type, it's quite clear that the extended complex numbers aren't supported either. So the complex projective line aka the Riemann sphere won't provide for zero division in (T-)SQL. I think user defined types with operations implemented in assembler (or some other language) could provide extended complex numbers or points on the complex projective line or on the Riemann sphere (with all the usual variations for exact and approximate numerics) in Postgres 😎 but that's quite possibly incorrect as it's remembering stuff that I looked at about aquarter of a century ago. I'm not sure TSQL-could do it: does T-SQL allow overloading of the divide operator ("/") with the extended complex divide alongside the built-in divides 😉 ? - I really must lean more about user defined types in T-SQL?
The extended complex numbers (and hence the Riemann sphere) are a nice example of a something which uses an arithmetic in which several of the usual arithmetic identities apply. It isn't a ring, it isn't field, and discarding the mutiply and divide operations doesn't leave an additive group :w00t:.
Tom
June 10, 2014 at 1:28 pm
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 10, 2014 at 1:44 pm
TomThomson (6/10/2014)
Koen Verbeeck (6/10/2014)
What? You are not by default talking about the Riemann sphere? 😀As neither SQL nor T-SQL supoprts a complex number type, it's quite clear that the extended complex numbers aren't supported either. So the Riemann sphere won't provide for zero division in (T-)SQL. I guess user defined types with operations implemented in assembler (or some other language) could provide extended complex numbers or points on the Riemann sphere (with all the usual variations for exact and approximate numerics) in Postgres, but I'm not sure TSQL-could do it: does T-SQL allow overloading of the divide operator ("/") with the extended complex divide alongside the built-in divides?
Somehow I struggle with accepting the justification for zero division used for projection (Riemann) when it counts for a fraction of a permille in terms of application.
😎
June 10, 2014 at 1:46 pm
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂
+1;-)
Often this coincides with learning or discovering something new!
😎
June 10, 2014 at 2:00 pm
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂
Another thing that really stinks of including the datatype in the name is when the datatype needs to change. For example what do you do if you have a column named nProductID and business makes some changes that forces you to allow characters. Now you either have to update every single line of code or deal with the knowledge that your prefix no longer matches the actual datatype. UGH!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 2:10 pm
Sean Lange (6/10/2014)
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂Another thing that really stinks of including the datatype in the name is when the datatype needs to change. For example what do you do if you have a column named nProductID and business makes some changes that forces you to allow characters. Now you either have to update every single line of code or deal with the knowledge that your prefix no longer matches the actual datatype. UGH!!!
Yeah we got away from that naming convention some time ago now, that was early into working with SQL for me. So my thought was "Well that's the way they are naming the fields...". Again early on and very impressionable. 🙂 I didn't know about this site back then to get that kind of great advice.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 10, 2014 at 2:11 pm
Sean Lange (6/10/2014)
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂Another thing that really stinks of including the datatype in the name is when the datatype needs to change. For example what do you do if you have a column named nProductID and business makes some changes that forces you to allow characters. Now you either have to update every single line of code or deal with the knowledge that your prefix no longer matches the actual datatype. UGH!!!
Absolutely agree, it seems to have very little added value. At my job, someone (I believe from Oracle) had the idea to prefix all columns with 2 characters (F for field and one character for the datatype). Something even more annoying is that someone decided to make all columns as char on our DW. I get some headaches because of all the inconsistencies.
I don't have that problem with variables. Specially when I use @Date, @cDate & @iDate in the same procedure with the same date in different data types.
Viewing 15 posts - 181 through 195 (of 271 total)
You must be logged in to reply to this topic. Login to reply