Palindromic games with the SQL MVPs

Itzik Ben-Gan posted a challenge to his blog and to the SQL MVP newsgroup. The challenge was to be able to use T-SQL to create palindromes from a list of words. The words would have to be unique, so that the list wasn't endless.

A handful of MVPs (including myself) pushed out potential solutions. All slightly different of course, because of the creativity possible within T-SQL. Itzik posted the results at and it turns out my query was the fastest. Go figure! I'm not sure that Adam (Machanic)  or Steve (Kass) tried to optimise theirs much. I imagine if we all tried to find ways to improve our queries, they could all become a bit faster again.

The key thing with something like palindromes is to build them up as you go, looking for candidate words that can continue the sequences from the start and end – and this makes them a great candidate for CTEs. I also wonder if you could easily build them up from the middle, but that would then mean keeping a track of where the middle was… and I'm not sure it would end up as fast. Maybe another day.

Offensive movies on a plane?

I was on a plane recently and saw the new Helen Mirren movie The Queen. It was a well-done film, although in many ways it was just a curiosity about how they would show the events, rather than wondering what would happen next.

One thing got me though… I thought at first that there was an occasional problem with the sound, but on the third time I realised what was happening there's a line when someone says "It was a vow before … " (I've put the dots where the sound cut out). And I realised that they were cutting the sound every time someone referred to God.

Considering that the Borat movie was also showing on the plane, it does make you wonder what the definition of 'offensive' is these days. And will the text used at the coronation of George VII be altered so that he no longer makes a vow before God, and isn't made the head of the Anglican church?

Two new certifications

Seems I have two new Microsoft Technology Specialist certifications. MCTS: Microsoft Windows SharePoint Services 3.0: Configuration and MCTS: Microsoft Office SharePoint Server 2007: Configuration. The links are to the exams I passed, not the certifications themselves. Seems the cert sites don't quite exist yet.

I sat the exams in November, during the beta period. I got the results in the past week. Seems these two exams each earn you a MCTS certification.

Set-based approach to finding consecutive records

I'm sure I've written about this before, but it comes up so regularly in the newsgroups that I thought I'd write an answer here.

A table (probably an audit table) exists with a field that stores a date signifying when a change was made. You need to return a result-set which has a row for each period of time between modifications.

For example:

CREATE TABLE QtyAdj ( …. , ModDate DATETIME, Qty INT )

–where I haven't listed all the columns – there should obviously be a primary key on this. In fact, I'll assume there is a field called ID which is unique.

What we want is:

with modnums as (select *, row_number() over (order by moddate, id) as rn from
select m_this.moddate, m_next.moddate, m_this.qty
modnums m_this
  left join
  modnums m_next
    on m_next.rn =
m_this.rn + 1

Which uses row_number() within a table expression to give a unique number to each row ordered by the date. You can then join between two copies of the table expression (love CTEs for that), to get your consecutive records in a single row of your result-set.

Powershell script from my SQL presentation

Last week I presented at my user-group about PowerShell and why every DBA should know this. The talk went for just over an hour, and as most of the audience hadn't used PowerShell at all, I started from the top and really pushed concepts like "You pipe objects not text". The script can be downloaded from here.

So then by the time I got around to talking about the fact that you can really easily hook into ADO and SMO, I think the audience were already caught on the idea that PowerShell really is very powerful and that anything you can do with SMO can be done really easily with PowerShell too. Most DBAs write scripts that use SMO to manage their SQL boxes. In the past they've used VBScript, but I think they should use PowerShell – it's got so much more going for it, and because it can replace cmd, there's almost no reason not to use it.

The talk didn't go into all the stuff you can do with SMO – that's a different presentation. This was a way of demonstrating that you can use PowerShell for SMO, as well as everything else you might want from a sysadmin perspective.

Still looking for the right laptop

Someone suggested I check out the Acer Ferraris. Yeah, seriously. So I checked out the specs. Frustratingly, Acer don't have them on the their list of products in Australia. The UK Acer site seems to suggest that the F1000 is actually quite well priced (although UK prices on hardware are often quite different to Australian prices).

But if Acer aren't selling them here, then that would make it a little awkward, don't you think…

In the market for a new laptop

What would you recommend? I want something that will happily run virtual machines without blinking, so that it can be an effective development environment as well as running all the server products I'd care to code against (SQL, WSS, etc). It has to be as good as it needs to be when I present at user-groups or train people.

The Dell D820 seems to be quite popular. HP's nw8440 seems okay, but maybe overpriced? Sony Vaios seem to be much lighter, but is that worth the extra expense? And what about IBM?

Love to read your comments… So far I'm leaning towards the Dell.

The soapy frog does ASCII Art in PowerShell

Of course the idea is the same as Unix's AALib, but what's so cool is how little code it takes to do this in PowerShell.

The idea is simply "Load up an image in System.Drawing, work out the brightness of each range of pixels, and from that, give it a character to represent the block of pixels."

So this is me:


So I guess the idea is to find cool things to do with PowerShell. AALib is a very cool idea, and that makes this code very cool too.

Dynamic friends list with Whooiz and Ajax

Whooiz is changing. They have a new logo, they're dropping the capital H (but it might take a bit of time for this one to go through), and the widgets are now much more dynamic.

So in my blog (if you're reading this from elsewhere, go to, I now have a much smaller 'whooiz friendz' section, and I only show one friend. It's not that I have offended everyone and only have one friend, because if you watch for a few seconds, you'll see the friend change to a different (random) friend.

This is really nice. It's something I was tempted to do myself before, but Clarke beat me to it (it's fair enough, he's the guy behind Whooiz). I told him I was going to do it, but just didn't get the time over the holidays to scrape through his script for the bits I wanted to change.

Unfortunately it now lives within an iframe, which gives a little less control over the CSS. My trick of setting the header to not display no longer works, but instead, you can now put '&noheader=y' on the end of the querystring, and that achieves the same (or at least, it will once Clarke has made the change for this).

The code I'm using is:

<div class="sideNavItems"><h3>Whooiz Friendz</h3>
<iframe id="Iframe1" frameborder="0" scrolling="no" height="200px" width="100%" src=""></iframe>

The 100% lets it fit nicely in the div tag, and 200px is what I reckon makes it fit nicely, although it could probably be adjusted down a little more.

Now, presumably this could be thrown really easily into a sidebar gadget… 

PowerShell and SQL

Because I've been playing with PowerShell more and more recently, I'm going to do a talk at my user-group about it next week. Should be a lot of fun, and a great excuse to get deeper into the technology over the weekend.

One thing that I think is really great is the ease with which you can apply things to a list, because of the pipe operator – which works just like the pipe operator in any other kind of shell, but actually handles the things as objects. In my example here, it picks up the fact that I'm passing in a list ($servers), so that I can then do a 'foreach' loop (using the % operator). I put it all in a process section so that the $sql command drops out of scope, releasing the object nicely (which makes it equivalent to setting it to nothing). I could also used Remove-Variable 'sql' to drop the reference, or just set $sql to something else. But I prefer to let it fall out of scope – it just feels a bit neater.

Please forgive the cases, this is just as I typed it…

$servers = 'server1','server2','server3'
& {
  $servers | % {
    $sql = New-Object ('') $_
    $sql.Databases | Select-Object @{Name = 'server';Expression = {$}}, name, lastbackupdate

The @{Name=;Expression=} bit lets me put something into the table which doesn't come from $sql.Databases, and hey presto, I have a nice table come out telling me when the databases were last backed up. I could easily push this through a Where-Object filter to only get ones that haven't been backed up for some time too.

Incidentally – you should run


first to make this work. I'm just so used to having this load up as part of my PowerShell profile these days…