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 «««4,1894,1904,1914,1924,193»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 2:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:02 AM
Points: 2,668, Visits: 19,229
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #1508600
Posted Friday, October 25, 2013 4:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 3,513, Visits: 7,567
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508623
Posted Friday, October 25, 2013 4:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 4,425, Visits: 3,417
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.
Post #1508624
Posted Friday, October 25, 2013 11:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 23,253, Visits: 31,944
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1508637
Posted Saturday, October 26, 2013 9:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1508663
Posted Saturday, October 26, 2013 1:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 8,690, Visits: 9,226
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
Post #1508672
Posted Saturday, October 26, 2013 2:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 42,774, Visits: 35,871
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1508674
Posted Saturday, October 26, 2013 2:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 4,425, Visits: 3,417
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.
Post #1508675
Posted Sunday, October 27, 2013 2:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 6,594, Visits: 8,877
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1508709
Posted Sunday, October 27, 2013 6:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:14 AM
Points: 3,618, Visits: 5,254
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1508720
« Prev Topic | Next Topic »

Add to briefcase «««4,1894,1904,1914,1924,193»»»

Permissions Expand / Collapse