October 24, 2013 at 8:08 pm
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!
Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.
Tom
October 24, 2013 at 10:52 pm
L' Eomot Inversé (10/24/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.
You've hit the nail on the head.
It would also be nice to see the internals of geometry and geography types use exact numerics internally or apply a tolerance that eliminates the float issues. There is nothing more frustrating than unioning a couple of perfectly adjacent polygons and having tiny slivers appearing because of float inaccuracies. It has got better with 2012, but still happens occasionally. As a person that deals with spatial data the vertex it is really important and should not change because of some operation.
October 25, 2013 at 7:00 am
mickyT (10/24/2013)
L' Eomot Inversé (10/24/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.
You've hit the nail on the head.
It would also be nice to see the internals of geometry and geography types use exact numerics internally or apply a tolerance that eliminates the float issues. There is nothing more frustrating than unioning a couple of perfectly adjacent polygons and having tiny slivers appearing because of float inaccuracies. It has got better with 2012, but still happens occasionally. As a person that deals with spatial data the vertex it is really important and should not change because of some operation.
This is something I've been interested in learning for a long time, but never had the opportunity to actually do work in the area. Sigh.
October 25, 2013 at 7:30 am
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!
Jason - this was a good article from both points of view.
Has Steve hit you up to follow this up for an article for SSC?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 25, 2013 at 8:12 am
WayneS (10/25/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Jason - this was a good article from both points of view.
Has Steve hit you up to follow this up for an article for SSC?
I think Steve's mind has been on the ski slopes this week and his body has now followed, so no, not yet. 🙂
Thanks for the feedback on the blog post, too. I'm feeling my way through this blogging process and like to know whether I'm on the right track or not.
Jason Wolfkill
October 25, 2013 at 2:55 pm
Greg Edwards-268690 (10/23/2013)
wolfkillj (10/23/2013)
Greg Edwards-268690 (10/23/2013)
Koen Verbeeck (10/23/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Nice blog post. Very good for a first one.
I don't work a lot with spatial data, but it's good to know I have to look out for approximate data types.
Look forward to your next post 🙂
Great example and explanation.
And good timing for me, as I am exploring some of the newer spatial stuff.
Thanks, Greg! As I mentioned in the post, I do a lot of work with geospatial data and have learned a TON about both the SQL Server implementation of the spatial data types and the underlying concepts. Working with geospatial data may be the "funnest" part of my job, in fact. What particular aspects of the spatial data functionality are you exploring? If there's a particular topic in that area that you'd like to read more about, I'm open to suggestions for future posts!
Currently I'm going to play around with Excel and the 2013 mapping with PowerPivot. At my previous job, we had mostly North American sales, with 2 sales orgs. One went by State, while the other zip / postal, but not necesarily by state. Mapping a zip / postal code for the whole globe seemed like you would have to pay for several services to get the data to map this to a better model. Something about what you have found for free sources would be of interest.
We had a current bookings process going into the cube that ran every 30 minutes, which I put into a map of North America in SSRS, which you could drill down to details for any state. Much more visual presentation than the normal grid. It also seems a more natural way to view the data. So something about what you have seen for reaction / adoption from your users might be good too.
MapPoint, and/or using the MapPoint object in VBA. Way easier for users, exploratory, generates routes, adds demographic information from census data, custom territories, etc.
not sure why this isn't just integrated into SSRS, but until it is, I'm not even wasting my time creating geo reports there.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 25, 2013 at 4:30 pm
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).
I wonder what's going on in the world if people claim all that experience without knowing the simple basics.
October 25, 2013 at 4:36 pm
Luis Cazares (10/25/2013)
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).I wonder what's going on in the world if people claim all that experience without knowing the simple basics.
I have been there before. I developed a simple trick: I ask the candidate how they rate their skills on a scale of 1 to 10. If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.
October 25, 2013 at 11:57 pm
Revenant (10/25/2013)
Luis Cazares (10/25/2013)
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).I wonder what's going on in the world if people claim all that experience without knowing the simple basics.
I have been there before. I developed a simple trick: I ask the candidate how they rate their skills on a scale of 1 to 10. If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.
What about someone with 16+ years experience that rates themselves a high 6 or a low 7? I don't care how long I have been using SQL Server, I only know what I know based on the needs of my employers and what I can learn on my own as time permits. I am sure there is much more to learn and experience with such a vast product.
October 26, 2013 at 9:38 am
L' Eomot Inversé (10/24/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.
STR is pretty bad for performance and can produce some really unexpected rounding errors even with smaller numbers. Please see the following article for some of what I'm talking about.
http://www.sqlservercentral.com/articles/T-SQL/71565/
My recommendation is to forget that STR even exists.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2013 at 1:46 pm
Jeff Moden (10/26/2013)
L' Eomot Inversé (10/24/2013)
wolfkillj (10/23/2013)
I've published my first technical blog post. I'd appreciate any feedback from this group on both the technical aspects and the writing. Thanks, everyone!Nice article. I liked seeing the use of STR for conversion as one option for solving the problems. Most people who write about approximate muneric problems appear to assume that the problem is rounding errors in the floating point arithmetic rather than conversion errors and representation errors, but you have spotted that the thing that needed addressing to fix these examples was conversion error (because convert and cast and implicity conversion can all cause errors by doing too much rounding and sometimes by rounding incorrectly, but STR doesn't because you can specify the precision wanted).
Until we get modern floating point in SQL, it will be sensible to use exact numerics for latitudes and longitudes; and even when we do we may still find that we have to use STR to avoid conversion errors if we use approximate numerics.
STR is pretty bad for performance and can produce some really unexpected rounding errors even with smaller numbers. Please see the following article for some of what I'm talking about.
http://www.sqlservercentral.com/articles/T-SQL/71565/
My recommendation is to forget that STR even exists.
Certainly STR causes problems when using it requires an implicit conversion from an exact numeric to an approximate numeric, because that conversion can introduce representation errors. But if one is starting from floating point that doesn't happen, because no implicit conversion is needed.
I guess I should go and try to discover whether a cast to decimal(38,18) followed followed by rounding to decimal(p,x) , where p and x are the required precision and scale for the output format, and then cast to varchar(p) has worse performance than just calling str when the starting point is float(53) but I take the view that the cost shown by the code example at the end of your spackle note on STR is largely caused by the implicit conversion from int to float which wouldn't be there if the starting point were a float, and when starting from float there isn't that performance penalty.
An interesting point that the wolfkillj article was making was that implicit conversion from float to varchar does the wrong thing and introduces errors that are not introduced by STR, and my point was that it good for an article to notice that because most authors are too busy believing that float rounding is worse than exact numeric rounding, which is the opposite of the truth: the rules for calculating the precision and scale of the result of an arithmetic operation on decimal values ensure that any long chain of calculation must include frequent precision-and-scale-reduction casts which introduce vastly greater rounding error than would an equivalent chain of calculation using FLOAT(53) throughout, which wouldn't need those casts.
That doesn't mean that I don't think STR should be avoided in every case where the starting point is not an approximate numeric: it certainly should, no-one would want to suffer representation errors arising from a pointless implicit conversion to float. But it shouldn't be forgtten altogether - if one has approximate numeric data that needs to be reported STR is a useful tool. Of course creating cases where STR is useful by using approximate numerics where they are not appropriate would be stupid - and generally they are appropriate only where long chains of calculation will happen or, sometimes, where the input is known to be approximate values, not exact ones. Approximate numerics as we currently have them are, as wolfkillj's blog entry pointed out, not a good way of representing lattitude and longitude if one wants to calculate the distance between two points. Another extremely good point made by the blog entry is that a believable wrong answer is worse than an unbelievable wrong answer.
Of course the whole problem should go away as soon as we get the latest floating point standard implemented in SQL, along with the conversions that it needs: either an improvement to STR to avoid conversion to the wrong sort of FLOAT or an enhancement to CONVERT to allow scale and precision to be specified for VARCHAR output when the input type isn't the wrong sort of FLOAT; this is because the one of the new FLOAT types suffers from representation errors only for values for which exact numerics have exactly the same errors. Maybe that kind of FLOAT should be classed as an exact numeric - but I suspect that will not happen. Actually I suspect that getting the latest floating point standard into SQL Server also won't happen, at least not any time soon.
Tom
October 26, 2013 at 2:10 pm
Revenant (10/25/2013)
If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.
I'd fail that test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2013 at 2:29 pm
GilaMonster (10/26/2013)
Revenant (10/25/2013)
If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.I'd fail that test.
You are an outlier, and I would know the difference. 🙂
October 27, 2013 at 2:31 pm
Revenant (10/26/2013)
GilaMonster (10/26/2013)
Revenant (10/25/2013)
If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.I'd fail that test.
You are an outlier, and I would know the difference. 🙂
You just forgot to include "and is not a MCM"...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 27, 2013 at 6:10 pm
Revenant (10/25/2013)
Luis Cazares (10/25/2013)
I'm losing faith in humanity here as I've been interviewing some candidates for BI developers at my job. The last one claiming to have 8 years of experience as .NET developer (using SQL Server) and knowing 75% of SQL Server couldn't make a simple query on his head (SELECT * FROM Product WHERE ProductName != 'Milk') saying he would use DISTINCT to filter values from a query and he used just jobs to import data from a flat file (no bcp, bulk insert or SSIS).I wonder what's going on in the world if people claim all that experience without knowing the simple basics.
I have been there before. I developed a simple trick: I ask the candidate how they rate their skills on a scale of 1 to 10. If the candidate has less than 12 years on SQL Server and rates himself - much less frequently herself - 8 or higher, the interview effectively ends right there.
I guess if you were interviewing me, that interview would be pretty short.
But I've been warned. I'll rate myself a 7 (even though that may or may not be true).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 41,896 through 41,910 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply