Month: October 2010

A few people have asked me about my community picks for the upcoming PASS Summit (in less than a week I’ll be on my way).

The community picks can be seen at http://www.sqlpass.org/summit/na2010/Agenda/CommunityExpertsPicks.aspx, and includes selections from several well-known people in the community, and me too! The idea was to pick a selection based on a particular theme, to help people choose what to see.

Amazingly – both Adam and Brent suggested people come along to my session. We’ll see how that goes.

Anyway – looking through the amazing list of topics, the idea of picking which sessions are definitely worth going to is impossible. So I picked a theme – MDX.

Why MDX? Mainly because it’s an area that so few people really get. There’s definitely a lot of interest amongst my clients in learning MDX, and I’m always happy to help. I’ve taught Chris Webb’s course before, and will do again. And for people interested in MDX heading to the Summit, there are at least five sessions that are well-worth attending.

This month’s T-SQL Tuesday has me caught slightly unawares. I’m in the UK, having come over for SQLBits, and I’m left writing this with only a few hours to go.

The theme this time is on misconceptions in SQL, and this fits nicely with SARGability – one of the topics that I presented on at SQLBits a week and a half ago. I often see people refer to SARGability as making sure that the left-hand side of the predicate is untouched. In other words, suggesting that something like this would be fine:

   

WHERE someColumn = someFunction(someValue) 

whereas something like this would not:

   

WHERE someOtherFunction(someColumn) = someValue 

As with most misconceptions, there’s an element of truth to this, but it definitely doesn’t tell the whole story.

If you have an index on someColumn, then great – the first predicate should let you find the value you want in that index nicely. Then you have other questions about whether the index is suitable or not, but that’s for a different post. On the other hand, if you don’t have an index, then the system is going to scan the table (or at least some less suitable index) in both scenarios. Applying the function to each column is painful in the second one, but SARGability is about producing seeks instead of scans, and these would both cause scans.

But how often do you write predicates like this. Normally, we have something more like:

   

ON x.someColum = y.someOtherColumn 

…and this naturally causes a question. Do we want to be looking up the values in x, or in  y? What needs to be SARGable here, the left or the right? It’s very easy to assume the left, but is it really going to be best for you? SQL Server doesn’t care which way around you’ve written your predicate, so don’t hold onto the ‘left-hand side’ idea longer than the end of this post. In fact, think about the way you want your query to be executed, and ask yourself where your SARGability should actually be looking.

It’s all a matter of perspective…