SQLBits pre-con – Fixing Queries with Advanced T-SQL

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.

Leaving your comfort zone and standing in front of an audience

ITSQL2sDay150x150 keep seeming to dip back into the training space with LobsterPot Solutions, and I think this is a good thing. Training is both challenging and rewarding. It’s very different to consulting – the other part of what the company does – but the two disciplines compliment each other very well. LobsterPot doesn’t run public courses, but we do get asked for training from various places, including existing clients, prospective clients, and even other training companies.

I’m not writing this as an advert for the training that we do, but rather as a reflection on the discipline and why I value it so much.

When you train people in something, whether it be a technical discipline, ‘soft skills’, or something else, it helps refine your skills in a way that you don’t get just from consulting. And vice-versa – if you’re just teaching, there’s a lot that you’ll miss out on. There are lots of lessons that you simply won’t pick up without real projects in the real world.

I feel for full-time trainers, partly because a lot of people who “only teach” are criticised for not having real world knowledge – and the same applies to people who have studied but never used their knowledge in a real project. But I also know (including from personal experience) that it’s very easy to get into a rut of doing something in a bad way, and having to stand in front of a different crowd of students quite regularly can force you out of that comfort zone.

Students will ask questions that you haven’t considered. As you work out explanations for things, you will find aspects that you don’t really understand as well as you thought. And you will be forced to address those issues. Just this week I’m due to teach an MDX course for a client, and it’s forcing me to consider how to address a particular aspect that I feel I haven’t really explored enough. I resolved an issue for this same client using the LinkMember() function, but on Friday I’ll be showing them that LinkMember() is generally less-than-ideal, and that a more thorough solution would be better. The solution that we had put in place using LinkMember() was a quick solution, which got them over a hurdle some time back, so I don’t regret it – I just know that preparing to teach this course has given me an opportunity to consider the drawbacks of that particular aspect.

Writing courseware can be even more challenging. When you find yourself needing to explain something, you end up discovering those gaps in your knowledge quite easily. And while you may be able to put your head in the sand to a certain degree, it’s definitely very humbling and you generally find yourself doing the necessary research to discover the answers (whereas when consulting, a workaround – like LinkMember() – may often suffice).

Having said all this, life should be a continual learning process. I hope that everyone reading this would agree that they can think of plenty of things they did in the past, that they would like to go back and do another way. If you can’t think of those things, then perhaps you need to be volunteering to teach people.

Getting an Advanced Competency in the Microsoft Partner Network

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.


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.


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


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.


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.


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.

Changes to the Microsoft Partner Program

I’ll miss Gold Partner status in the Microsoft Partner Program I think.

It’s not that my company LobsterPot Solutions will no longer qualify for Gold status, but that the Program is changing. It’s becoming the Microsoft Partner Network instead. These changes are rolling out at the moment, and October 2010 is the day when the big switch is being made. And I should make it clear that I don’t work for Microsoft, and I can’t guarantee that there aren’t mistakes in what I’m writing.

Goldx4 Under the Partner Program, partners could earn points by having customer references, employing MCPs, and various other things. If you reached 50 points, this meant the Microsoft Certified Partner brand could be used, but if you reached 120 points, the Microsoft Gold Certified Partner brand would apply. For LobsterPot, we enrolled in the program when it became clear we would have the 50 points, but surprisingly quickly reached 120. Within the program, we have “competencies” in Business Intelligence, Data Management, Web Development and in Learning Solutions. But we specialise in the first two. Always happy to provide some Web Development or Training, that’s very much part of the business, but the core is SQL Server.

Under the Partner Network, the points system is being removed, and the tiers will be put on the Competencies, rather than the overall membership. LobsterPot will no longer be a Gold Certified Partner, but will be a Partner with Advanced Competency. And getting an Advanced Competency is becoming much harder than before.

There will still be Standard Competencies, which have similar requirements to the current set of competencies under the Partner Program. Typically, these require two MCPs and three Customer References. And the MCPs could contribute to other competencies, so that a two-man shop could easily pick up a number of competencies if they’re working in that area.Image from Partner site

But the goal will be the Advanced Competencies. These require four MCPs (although an MCM counts as two), and these MCPs must have passed the higher-level certifications such as MCITP or MCPD. Furthermore, these MCPs cannot contribute to more than one Advanced Competency. So to have three Advanced Competencies, twelve different MCPs would be required. The number of customer references required has gone up from three to five. And there are other requirements such as an amount of revenue (dependent on a number of factors which I believe are still unclear).

Do I like the idea of losing Gold Partner status? No – I think “Microsoft Gold Certified Partner” is a strong brand, and it’s a shame to lose that.

Do I like the idea of having an Advanced Competency that differentiates LobsterPot from other companies that only have Standard Competencies? Yes! This should mean that potential clients have an easier time of realising that we’re more interested in giving someone a BI solution than giving them a pretty web site – but that we can do both if needed.

My conclusion is that it’s generally a positive move, but I have had to make a bigger effort to make sure enough of my staff have MCITP, and it’s a shame that we’re not large enough to pick up the Advanced Competency in both BI and Data.

For more information, please go to http://partner.microsoft.com, which should be regarded as the authority on all this.