Presentations about SARGability tomorrow

Tomorrow, via LiveMeeting, I'm giving two presentations about SARGability. That's April 27th, for anyone reading this later. The first will be at the Adelaide SQL Server User Group. If you're in Adelaide, you should definitely come along. Both meetings are being broadcast to the PASS AppDev Virtual Chapter.

The second will be in the Adelaide evening at 9:30pm, and will just be and my computer. The audience will be on the other end of a phone-line, which is very different for me. I'm very much used to being able to see my audience, rather than just being in a room by myself. The level of interaction will be slightly smaller, so I might have to find a laughter track to play when I say something funny. (Don't worry – I won't actually do that… I'm used to telling jokes and hearing nothing but crickets)

The first session is at 12:30pm in Adelaide. That's 1pm on the east coast of Australia, 3pm in New Zealand, and 8pm on Monday 26th in Seattle. If that doesn't suit you, then it's being "repeated" at 9:30pm in Adelaide, which is 1pm in the UK, or 8am on the east coast of the USA. The links to join the LiveMeeting session are: 12:30pm Adelaide and 9:30pm Adelaide

So what is this SARGability thing, and why is it important to you?

Essentially it's about making sure that your indexes can be used properly, so understanding it will help you make your queries run better – and it's not a hard thing to pick up, either. We'll be looking at the basic concepts, some misconceptions that people have, and then extending the ideas to the point that (hopefully) will make you want to re-examine some old queries to see if you had really been letting the Query Optimizer do its thing.

I think the sessions will be recorded, but I'm also very happy to be answering plenty of questions as we go along. If you want to jump in and send me queries, I'll happily give time to looking at them.

SQLBits VI – The sixth sets

My involvement stopped with the tagline, but SQLBits VI is on tomorrow. The theme of the event is Performance Tuning, which has nothing to do with Bruce Willis or dead people – unless Bruce Willis has just become a database expert and been shot for doing a dropping an index (some would say that's a crime worthy of the death penalty).

It's a shame my involvement hasn't been more, because it's such a terrific event, and it would've been good to have been there for a second time. It's a long way to go though, all the way from Australia to London.

The sessions on the agenda look great. Conor Cunningham from Microsoft (the guy behind the Query Optimizer) is headlining, which is making me wish I could be there even more.

So this post is to merely wish everyone who is going (and particularly the organisers) a great time. I hope you enjoy it thoroughly, and come away wanting to tune stuff!

Handling special characters with FOR XML PATH(")

Because I hate seeing > or & in my results…

Since SQL Server 2005, we've been able to use FOR XML PATH(") to do string concatenation. I've blogged about it before several times. But I don't think I've blogged about the fact that it all goes a bit wrong if you have special characters in the strings you're concatenating.

Generally, I don't even worry about this. I should, but I don't, particularly when the solution is so easy.

Suppose I want to concatenate the list of user databases on my system, in alphabetical order. Easy: Note the lack of

This is easy, and it just works. The STUFF command lets me strip the leading comma and space, and my list of database names is done.

But if I wanted to list the names of database with triangular brackets around them… then that's bit more complicated.

It still runs, but I my results don't show the triangular brackets, it shows <databasename>, <databasename2>. It's not what I want to see. I effectively need to HTMLDecode the results – but T-SQL doesn't have a function like that.

However, if I actually make my FOR XML call return actual well-formed XML, then I can extract the contents out, and it returns the data to me in its original (correct) form.

To be valid XML, it needs a ROOT element. I also need to tell it to be XML, using the TYPE directive.

But this lets me hook into the value of /MyString[1], and return that as varchar(max).

And it works, my data comes back as <databasename>, <databasename2>, etc.

It's a habit I need to use more often.

Edit: I can also skip the ROOT element (but keep TYPE) and use .value('.','varchar(max)') – but for some reason it's always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed.

Exploding maps in Reporting Services 2008 R2 – T-SQL Tuesday #005


Well, that was the imagery that secretly appeared in my mind when I saw "USA By State Exploded" in the list of installed maps in Report Builder 3.0 – part of the spatial offering of SQL Server Reporting Server 2008 R2.


Alas, it just means that the borders are bigger. Clicking on it showed me.


Unfortunately, I'm not interested in maps of the US. None of my clients are there (at least, not yet – feel free to get in touch if you want to change this 'feature' of my company).

So instead, I've recently been getting hold of some data for Australian areas. I've just bought some PostCode shapes for South Australia, and will use this in demos for conferences and for showing clients how this kind of report can really impact their reporting.

One of the companies I was talking about getting shape files sent me a sample. So I chose the "ESRI shapefile" option you see above, and browsed to my file. It appeared in the window like this:

08-Apr-10 11-42-43 AM

Australians will immediately recognise this as the area around Wollongong, just south of Sydney. Well, apart from me. I didn't. I had to put a Bing Maps layer behind it to work that out, but that's not for this post.

The thing that I discovered was that if I selected the Exploded USA option (but without clicking Next), and then chose my shape file, then my area around Wollongong would be exploded too!


Huh! I think this is actually a bug, but a potentially useful one!

Some further investigation (involving creating two identical reports, one with this exploded view, one without), showed that the Exploded View is done by reducing the ScaleFactor property of the PolygonLayer in the map control. The Exploded version has it below 1. If you set to above one, your shapes overlap.


I discovered this by accident… I guess I hadn't looked through all the PolygonLayer options to work out what they all do.

And because this post is about Reporting, it can qualify for this month's T-SQL Tuesday, hosted by Aaron Nelson (@sqlvariant).

My new laptop – with a really nice battery option

It was about time I got a new laptop, and so I made a phone-call to Dell to discuss my options. I decided not to get an SSD from them, because I'd rather choose one myself – the sales guy tells me that changing the HD doesn't void my warranty, so that's good (incidentally, I'd love to hear people's recommendations for which SSD to get for my laptop). Unfortunately this machine only has one HD slot, but I figure that I'll put lots of stuff onto external disks anyway.

The machine I got was a Dell Studio XPS 16. It's red (which suits my company), but also has the Intel® Core™ i7-820QM Processor, which is 4 Cores/8 Threads. Makes for a pretty Task Manager, but nothing like the one I saw at SQLBits last year (at 96 cores), or the one that my good friend James Rowland-Jones writes about here.

imageBut the reason for this post is actually something in the software that comes with the machine – you know, the stuff that most people uninstall at the earliest opportunity. I had just reinstalled the operating system, and was going through the utilities to get the drivers up-to-date, when I noticed that one of Dell applications included an option to disable battery charging. So I installed it. And sure enough, I can tell the battery not to charge now.

Clearly Dell see it as a temporary option, and one that's designed for when you're on a plane. But for me, I most often use my laptop with the power plugged in, which means I don't need to have my battery continually topping itself up. So I really love this option, but I feel like it could go a little further. I'd like "Not Charging" to be the default option, and let me set it when I want to charge it (which should theoretically make my battery last longer). I also intend to work out how this option works, so that I can script it and put it into my StartUp options (so it can be the Default setting). Actually – if someone has already worked this out and can tell me what it does, then please feel free to let me know.

Even better would be an external switch. I had a switch on my old laptop (a Dell Latitude) for WiFi, so that I could turn that off before I turned on the computer (this laptop doesn't give me that option – no physical switch for flight mode). I guess it just means I'll get used to leaving the WiFi off by default, and turning it on when I want it – might save myself some battery power that way too.

Soon I'll need to take the plunge and sync my iPhone with the new laptop. I'm a little worried that I might lose something – Apple's messages about how my stuff will be wiped and replaced with what's on the PC doesn't fill me with confidence, as it's a new PC that doesn't have stuff on it. But having a new machine is definitely a nice experience, and one that I can recommend. I'm sure when I get around to buying an SSD I'll feel like it's shiny and new all over again!