T-SQL Tuesday #33 – Trick Shot
August 14, 2012 Leave a comment
T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.
The topic of this month is trick shots. Thinking about this subject, I decided to search for the literal translation on Wikipedia:
“A trick shot (also trickshot or trick-shot) is a shot played on a billiards table (most often a pool table, though snooker tables are also used), which seems unlikely or impossible or requires significant skill.”
So a trick shot is a trick that’s unlikely or impossible. Isn’t that something we hear everyday? I know I do! A few quotes I overheard last week:
“No, this can’t be done differently”
“No, this cursor is set-based”
“I have this query (500 lines of code), and it doesn’t do what I want/what I build. Can you fix it?”
In at least 2 cases it ended up with me being right (unfortunately for them, and our company). But fixing the issues took some time, a lot of talking (or, as a manager would call it, coaching), and a fair deal of patience. But then came the hard part: rewriting the code. How do you rewrite a query, based on a cursor, into a set-based operation? There’s your trick shot! 🙂
Another example of a “trick shots” was creating a solution for a spatial data problem. My colleagues created an application that saves polygons into the database. But unfortunately, it we didn’t use the .MakeValid() function. That meant that some polygons were invalid, and some objects actually contained more then 1 polygon (which should have been stored as multi-polygon). When querying this data, the execution of the query retrieving the geography object failed because of the invalid objects. Finding these objects it the biggest issue.
Well, surprise, a cursor was the solution! For every string of coordinates retrieved, I entered a TRY-CATCH block. There I tried to convert the array of coordinates int a valid polygon. And if it failed, I added it to a memory table of invalid polygons that I declared in the query. The results from the table were printed at the end of the script, so the developer that ran the script could fix them. Normally I’m against the usage of cursors, but sometimes a cursor is usefull to find a problem, as you just saw.
What I’m actually trying to say, it that is that a trick shot isn’t unlikely or impossible, as long as you know what you’re doing. It takes a lot of practice, trial and error, and patience to master a certain skill. And, like in most cases, the only thing that’s holding you back is your own mind and imagination. So try to search for new things, keep challenging yourself to learn new things!