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.

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.

Luis C.Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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.

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.

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.

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.

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.

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