I received an email from someone who’s trying to decide whether or not to register for my precon at SQLBits 7 next month. He’s already coming to the event (which should be a given for anyone who can make it), but he was trying to work out a few things about my precon, which is called “Fixing Queries through Advanced T-SQL Techniques”.

He wrote:

Hi Rob,
I'm trying to decide on my SQLBits 7 choices and am looking at your Precon Thursday session hence I had a few questions.
I've (mostly) administered and developed in sql server for 10 years hence trying to gauge the level / suitablility.
How much do you go into execution plans?
Will there be any printed material for the course?
I find I learn from repetition hence dont want to do a course and forget it all by the next day!
Is there any reading you suggest?
Sorry to be fussy, paying from my own pocket not a company training budget you see!

I don’t think he’s very fussy at all. Paying for training out of your own pocket can be scary. Last thing you want is to feel like it’s a waste. So I replied:

Hi,

Execution plans will be a major part of the day. Just about every aspect of the day will look at the effect of various constructs on the plan, as the plan is where many performance issues can be seen.

As far as printed material is concerned - I wasn't planning on giving much out before the event, because the day will be so focussed on queries and seeing how things are reflected in the plans (and I find this can be much more easily expressed verbally and with hand-waving than in written form). I will certainly provide the queries that are used, but was looking to focus on making you think, not read. The key points will be inserted as comments in the queries, so I do expect that you'll have plenty of material to remind you what was seen once you get back to work - hopefully this will be more useful than giving you notes ahead of time. Plus, I'll always answer emails about the content.

For reading material, I'd make sure you're familiar with basic execution plan concepts, as that will probably give you an advantage over many of the people in the class. My hope is that you'll leave the day with the skills to be able to evaluate the plans that your queries are producing, and have learned ways to improve them. There is an excellent chapter on Execution Plans in the book "Inside Microsoft® SQL Server(tm) 2005: Query Tuning and Optimization" - but I will also be explaining the key points for those less familiar with plans.

To really work out if this precon is for you, watch the video from SQLBits V at http://sqlbits.com/Agenda/event5/Designing_for_simplification/default.aspx. If this is stuff that interests you, and you want more (and in particular, looking at real queries), then definitely come along.

Hope this helps,

Rob

And if you (the reader) are trying to decide whether or not to do a precon (particularly mine), then I hope this helps you too. I can’t answer tonight’s questions for the other precon presenters, I can only comment on the type of things that I’ll be covering, but I can thoroughly recommend all of the things on offer. The list of presenters is tremendous, and I think this really helps demonstrate that SQLBits is very much a world-class conference.

I’m giving a pre-con seminar at SQLBits 7 on September 30 this year in York (the Old one, in Yorkshire – similar to the New one, but with different songs). Let me quickly explain a few things about it – to help you persuade your boss to let you attend (and when you’ve done the persuading, go here and pick the “Full Conference” option).

It’s on the topic of Advanced T-SQL, but from a very practical perspective. I’m not going to be going into the uses of ranking functions or recursive CTEs as I have done with courses I’ve written in the past. Instead, I’ll be spending time looking at a bunch of things that I find most people don’t know about their queries, and show how various things can impact those queries’ execution plans. So it’s not so much Advanced features, but deeper aspects of the T-SQL you already know.

For example, I’ll look at a query that Denny Cherry and I discussed a while back, and consider how various things could be addressed using aspects of T-SQL such as sorting by aggregated fields with GROUP BY. By considering the impact of something like this, seeing where it can be used safely and where it can’t be used safely, hopefully you will be able to make better decisions about your T-SQL code and the methods you choose to solve problems.

Why do I want to speak on this topic? After all, it can be a lot of fun to talk about ranking functions or recursive CTEs – that’s functionality that a lot of people don’t really leverage, and it can help solve a lot of problems. But the things that I’m going to cover will help your overall understanding of T-SQL, and give you practical ways to improve a lot of the queries that you’ve written in the past. I will show you how to make your queries simpler, but better.

I remember one of the first times I explained some of these things to someone. They said “Wow, you’re making me want to go back and reconsider every query I’ve ever written.” That’s the kind of feedback that I’m hoping to get from the attendees.

Some of the points I’ll make will overlap will things that I covered in my SQLBits talk from last November (as reviewed by Phil Nolan – it was also Ian Russell’s favourite talk), and things that I covered in the 24 Hours of PASS in May (as reviewed by HanSQL). They were both popular talks, and hopefully people who attend the whole day of material will also get a lot out of it.

Being a pre-con, it does have a cost associated with it (the free event is on the Saturday). But I guess it comes down to the value. If you have a budget for training this year and want to find something to spend it on, then the paid portions of SQLBits (including my seminar of course!) are definitely worthwhile. If you don’t write T-SQL, then maybe one of the Chris’ (Webb or Testa) talks on SSRS or SSAS, or Brent Ozar’s session on Virtualisation could be for you (but be careful – he spells Virtualisation with a Z), or Buck Woody’s session maybe. There are seven to choose from (the others are Klaus and Maciej), and I’m sure they’ll all be excellent.

So get yourself to sqlbits.com and start planning a trip to Yorkshire.

I’ve also submitted some sessions for the main conference, so if you’re keen to see more, get up to York for the whole three days of Sep 30 to Oct 2 and be prepared to learn something. Having been a part of SQLBits V in South Wales (also the Old one), I can assure you this is one of the world’s top SQL events and shouldn’t be missed. It’ll also be a highlight for me as my latest year of being an MVP ends on September 30. Hopefully October 1 will bring a renewed award, but if it doesn’t, then this pre-con will be the last thing I do as an MVP.

A work in progress, I’m sure, as the particular requirements haven’t actually been made clear yet.

I previously wrote a post about the change from Partner Program to Partner Network, and this follow-up post goes into more information about what’s required for my company to pick up an Advanced Competency.

The Microsoft Partner Network site contains a document called Value of Earning a Microsoft Competency Guide, which describes all the competencies and how to achieve them. It’s a PDF with 109 pages, but actually there will only be a few of interest to you. The first ten pages are interesting because they cover general things, but if you’re already familiar with the way the Partner Network works, the interesting part is later.

For me, the Business Intelligence and Data Platform competencies are of interest, so I’m most interested in pages 18 and 19, where it describes how to achieve the BI one, and pages 30 and 31 for Data.

Here I’m going to look at the two competencies applicable to us at LobsterPot Solutions, although the requirements are very similar for other competencies.

MCPs

For the Standard competency in BI, we need two MCPs, each having passed both a BI exam and an exam in a related technology (such as the TS exams for SQL Server, SharePoint, Visio and Virtual Earth). This is a recent change to the competency, because a few months ago a SQL exam such as 70-431 would have sufficed. For LobsterPot this isn’t a problem, as we have enough people for this.

For the Advanced BI competency, the non-BI related exams don’t come into it. Four MCPs who hold MCITP: BI Developer 2008 are needed (although someone with MCM would count as two – not sure why, as there’s no BI component to MCM).

For Data, things are slightly easier – only one exam for the Standard competency, and for Advanced a choice of either MCITP: DBA 2008 or MCITP: DBD 2008 for each of the four.

The biggest impact here is that the four people contributing to the Advanced competency cannot contribute toward any other Advanced competencies. Therefore, to have Advanced in both BI and Data, eight staff members would be needed.

References

Customer references are still required. The Standard competency needs three, the Advanced needs five. No problem there.

Fee

Like in the Partner Program, there’s a fee involved. But if you want an Advanced competency, the fee is bigger. Hopefully this is worthwhile, but there’s no real problem achieving this, you just write a larger cheque.

The fee is for Advanced Membership, I don’t think it’s a fee which goes up for each Advanced competency that’s held.

Assessments

A new thing for the Partner Network is Business Training and Assessments. Now, either level of competency needs someone to have passed an assessment (or exam) in Licensing. I guess this is to make sure that partners are giving correct advice about licensing, although I think the more accurate answers about licensing will always come from Microsoft themselves.

And as well as this, someone (or two people for Advanced) needs to have passed a “sales and marketing competency assessment”. I’m not sure what this means, and I’m very curious about it because LobsterPot doesn’t have salespeople. All our work comes from references. I have no problem doing this assessment myself and putting one of my staff through it as well, but it does seem a little strange, and I hope it’s not a hurdle.

Revenue Commitment

The other new requirement for the Partner Network is that the Advanced Competencies need a revenue commitment, which is dependent on the competency and the location of the partner. I’m hoping this means that Sydney-based partners have to bring in more revenue than Adelaide-based ones, as the market in Sydney is bigger. It may just mean that Australian partners have different requirements than New Zealand ones.

This last point is the one that scares me the most. We charge rates that work in the Adelaide market, which I’m sure is different to what works in Sydney. I have no idea how much revenue we will need to commit to, or how it will be measured by Microsoft. Also, I assume that the revenue commitment to achieve both Advanced competencies could be more than we’d like.

Conclusion

Our plan at LobsterPot is to have one Advanced Competency (in Business Intelligence) in October as soon as it becomes available. Whilst I don’t yet know all the details, I do know that there will be many companies in our shoes, and I hope that Microsoft make the transition easy enough for companies like ours.

Disclaimer: Please understand that all the information here is my interpretation of the information contained at http://partner.microsoft.com, which should be considered the authoritative resource on the matter.

So the pre-/post-con submission I made for the PASS Summit in November didn’t get picked up (although I’m still hoping to get a regular session or two), but in the meantime, I have other presentations going on.

Those who participated in the 24 Hours of PASS event last month will know that I was a late inclusion when one of the speakers couldn’t make it. Those sessions are now available for free download (but you need to sign up if you’re not already a PASS member) at http://www.sqlpass.org/LearningCenter/24Hours.aspx – mine was Session 20.

Late next week, I will be presenting to the chapter in Columbus, Ohio (via LiveMeeting, as it’s a long way to go from Adelaide, a trip which would involve kayaks). I’ll be doing some more investigation into T-SQL techniques, but not the same ones I covered in the 24HOP session. I figure that since I was prepared to do a whole day of stuff, that can easily cover several different User Group presentations without doubling up.

And then in early August, I’ll be doing another presentation for the PASS AppDev Virtual Chapter. Also via LiveMeeting. I haven’t worked out a topic yet, or even the times of day, but it’s the August 10th gathering.

So if you enjoyed my 24HOP session (as I hope some of you did), or you were unable to listen because of time zone constraints (it was during the early hours of the morning for most Americans), then why keep your eye out for the links to these sessions and join us.

Displaying information on an interactive map really isn’t that hard to do. My friends John & Bronwen have been doing this stuff for ages through their company Soul Solutions, and have done lots of talks about how to do this. They have the ability to take that stuff far beyond what I can, but as it’s a significant aspect of data visualisation, it’s an area in which I like to be fairly fluent.

I wrote a blog post last month on how to fetch latitude and longitude values for addresses, which I did using PowerShell, but the same technology applies just as easily in any language capable to calling web services (including SSIS, it’s worth noting). I love how simple it is to do this, and being .Net, it’s even very straight forward to make a CLR object that can be called using T-SQL.

So once I have the latitude and longitude, I can easily get that into my database.

For example, if I look up an address in Chicago, I might get Lat/Lng as 41.885889, –87.632323. This is the location of PASS HQ. To use this with the spatial type, I can use something like: select geography::STGeomFromText('POINT(-87.632323 41.885889)',4326). And of course, once I have a database with that information, I can easily write some code which can loop through them, adding pushpin images to a Bing Maps Silverlight control.

And now, PASS have linked to the map I put together on their site. So if you go to http://www.sqlpass.org/PASSChapters.aspx, you can more easily find a chapter location near you (or wherever you want to go).

This map (not the screenshot of it above) lets you zoom in, switch to aerial, everything you’d expect from a Bing Map, and if you mouse-over the pushpin, you can see some more information about the particular chapter. If you’re a chapter leader, and want to get some information updated, I’m sure you can get that information to me somehow…

Interestingly, my favourite new feature of SQL Server 2008 R2 isn’t any of the obvious things.  You may have read my recent posts about how much I like some of the new Reporting Services features, such as the map control. Or you may have seen my presentation at SQLBits V on StreamInsight, which I think has great potential to change the way many applications handle data (by allowing easier querying of data before it even reaches the database). Next week the Adelaide SQL Server User Group has a session about MDS. There are plenty of really cool things in SQL 2008 R2.

But actually, nothing that appears in any What’s New lists are my favourite features of this new release.

My favourite thing about SQL 2008 R2 is in the name. It’s the fact that it is an R2. The same product, just the second version of SQL 2008. This means that people who are currently running SQL 2005 should be able to embrace SQL 2008 now (in its R2 form). I keep hearing the “I’m waiting for SP1” line, except that many organisations seem to have missed that SQL Server 2008 SP1 got released last August.

There is a healthy paranoia about data that means that many organisations are often very slow to upgrade systems. This is a good thing, despite the fact that it frustrates database professionals like myself, who would like to see clients upgrade to take advantage of new features. But new features aren’t things that can persuade someone to upgrade their currently-working-just-fine-thanks-all-the-same system until many others have tested the waters.

And this is where R2 can come into play. Technically it’s a new version, yes – but it’s a .5 release. It sorts out a few things, such as Unicode compression, and it adds features that are somewhat peripheral, but on the whole, the product is unchanged – like an overly significant service pack.

So my hope for R2 is that we see a new influx of organisations adopting it. It’s very good that it wasn’t called SQL Server 2010. That could’ve introduced a renewed paranoia about the stability of the product, with most people waiting for another service pack (or two) before rolling it out. Calling it R2, and acknowledging the lack of differences in the Database Engine and Analysis Services is in many ways, the best feature of SQL Server 2008 R2.

Regular readers of my blog (at sqlblog.com – please let me know if you’re reading this elsewhere) may be aware that I’ve been doing more and more with spatial data recently. With the now-available SQL Server 2008 R2 Reporting Services including maps, it’s a topic that interests many people.

Interestingly though, although many people have plenty of addresses in their various databases (whether they be CRM systems, HR systems or whatever), my experience shows that many people do not store the latitude and longitude co-ordinates for those addresses.

Luckily, the Bing Maps API provides everything you need!

Start by going to bingmapsportal.com, logging in using a LiveID and creating an account:

Then you can create a key using the link on the left. This key will be attached to a website, and looks something like: Apsjm7zVthPFMxlfpQqKhPPZrAupI-_aGH-CvT2b... Now you can use the Bing Maps API to fetch the information you need. Obviously check the terms and conditions to see if you will need to pay for your usage or not. The Bing Maps API works through web services, so it’s easy enough to use almost any system for this. You could easily make a CLR Function for use within T-SQL, but I’m going to show you how to do it using PowerShell.

Let’s start by creating a Web Service Proxy to the URL of the webservice.

$ws = New-WebServiceProxy -uri http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl;

By passing $ws into Get-Member (using the command: $ws | Get-Member), we can see that there is a Geocode method, which requires a parameter of type GeocodeRequest. Actually, the type to use is much more complicated, but it’s easy to create a variable for it using:

$wsgr = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.GeocodeRequest;

This variable will take the address to look up in its Query property, but we’ll do that in a moment. First we need to provide credentials, which is that key we created on the website. I’ve stored mine in a variable called $key, so that I don’t have to display it in demonstrations that might be recorded.

$wsgrc = new-object Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy1ervice_geocodeservice_svc_wsdl.Credentials;
$wsgrc.ApplicationId = $key;
$wsgr.Credentials = $wsgrc;

Now when we call the method on our web service, Bing Maps will know that it’s us that have called it.

Now I can make a request. If I use a single address, I can just use the Query property of the GeocodeRequest object, as I mentioned earlier. When I get my results from the Geocode() call, I can get multiple lines, and each of them has a bunch of useful information including (as I find most useful), the Formatted Address, and location co-ordinates. I can easily display this by passing the Results into a Select-Object call. I’m just handling the first result of each call, as will become clear in a moment.

$wsgr.Query = 'Adelaide, Australia';
$wsr = $ws.Geocode($wsgr);
$wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};

As readers familiar with PowerShell will already appreciate, there is a good potential for looping through many addresses. I did this with locations in the world that have PASS chapters recently, but to simplify this, my example uses just four.

$uglist = 'Adelaide, AU', 'Aukland, NZ', 'South Africa', 'San Deigo, USA';
$uglist | % {$wsgr.Query = [string] $_; $wsr = $ws.Geocode($wsgr); $wsr.Results[0] | select {$_.Address.FormattedAddress}, {$_.Locations[0].Longitude}, {$_.Locations[0].Latitude};}

This gives the following results:

$_.Address.FormattedAddress                           $_.Locations[0].Longitude                $_.Locations[0].Latitude
---------------------------                           -------------------------                ------------------------
Adelaide, Australia                                            138.599731698632                        -34.925769791007
Auckland, New Zealand                                          174.765734821558                       -36.8473847955465
South Africa                                                   25.0630002468824                       -29.0459994971752
San Diego, CA                                                 -117.161724865437                        32.7156852185726

You’ll notice that the FormattedAddress property shows the address in a standard format. This is great, because it will handle spelling mistakes (see how I left the ‘c’ out of ‘Auckland’ when I wrote it – for me I did this on purpose, but in most user-input systems, spelling mistakes are a common problem), and it will provide a consistency for punctuation, abbreviations, etc. Notice that I used ‘AU’, ‘NZ’ and ‘USA’, which were all transformed into something else in the web-service call.

With the Lat/Long details here, it’s very simple to get this into a database, or a file, or whatever format is required. For me, I put them into a database along with all the other PASS Chapter locations I had looked up (using the public data from the website), and using the Bing Maps Silverlight control, came up with something like this:

It was a bit more work to colour the pushpins by the region, and putting tooltips in with extra information, but bridging the gap between a pile of addresses and a map is actually remarkably straight-forward with the Bing Maps API.