Another new SSMS 2008 goodie – moving files into a project

So many times I'm writing a query, and I think "I want to put this into that project…", but because I didn't add it when I first made the query it's a pain (and I end up copying the text into a new query).

But in SSMS 2008, I've just noticed the File menu has a "Move <file> into project" entry down near the bottom. So now I can just choose that and my query magically appears in the project. Terrific!

There seems to be so many nice little things in SSMS 2008 – try right-clicking on some data in the results grid, and you'll see "Copy with Headers". Really useful! I recommend grabbing SSMS 2008 and using it whenever you would've normally used SSMS 2005.

Map of the world in the Spatial Results tab (and converting from Geometry to Geography)

One of the things on my list of stuff to check out in the RTM version of SQL Server Management Studio in 2008 is the Spatial Results tab that Isaac Kunen talked about a while back. He called it Eye Candy, and it really is.

I started by grabbing a set of data from Virtual Earth expert (and good friend) John O'Brien, containing geometry figures for the world. Geometry for things that are on the world isn't exactly the best, but it was better than nothing. So then there was the matter of converting it to the Geography type. Geometry is on a flat plane, Geography is on the world – understanding the spherical nature of it.

But either way, it was still very nice to query.

Converting the data into the Geography type was the next challenge. I started by adding a column which was the right type, and tried the simple:

update dbo.WorldBorders set geog = geography::STGeomFromText([geom].ToString(),4326)

It failed. Seems that there were problems with some of them. So I tweaked my script a little to try them all individually, setting the ones that couldn't be done to POINT(0 0). For this, I used GO n to run it a bunch of times (but I could've checked @@ROWCOUNT in a WHILE loop to be a little more precise). And I used the system of updating a derived table to make sure that I could update the first record each time.

begin try
    update w
    set geog = geography::STGeomFromText([geom].ToString(),4326)
    from (select top (1) * from WorldBorders where geog is null order by iso2) w
end try
begin catch
    update w
    set geog = 'Point(0 0)'
    from (select top (1) * from WorldBorders where geog is null order by iso2) w
end catch
go 250

This converted the ones that were possible, and this script:

select * from WorldBorders
where geog.STAsText() = 'POINT (0 0)'

world …showed me that the countries that didn't convert were Canada, Fiji, Italy, Cote d'Ivoire, Antarctica, Russia, United Kingdom and Svalbard. And you can see that from the Spatial Results tab too. Fiji and Svalbard are clearly missing, right? I love the Spatial Results tab – when you have a query which involves Spatial data, it just appears, containing a graphical representation of the data. You can even change the projection if you're not happy with the one that it shows by default.

As for why they're missing? Well, I haven't got around to looking yet. Probably, one of the shapes involved is listed in the wrong order (which is important for geography, but not for geometry). Also could be that Antartica, Russia and Canada are just too big.

When I tried to count how many polygons there were for each one, using:

select *, geom.STNumGeometries()
from WorldBorders
where geog.STAsText() = 'POINT (0 0)'

I found that some of the geometry fields weren't valid. So I tried:

select *, geom.MakeValid().STNumGeometries()
from WorldBorders
where geog.STAsText() = 'POINT (0 0)'

…instead, which worked (telling me I have 954 polyons, including 478 from Canada alone). I may have wrecked some of my shapes using MakeValid(), but hopefully it will be okay. Svalbard converted okay by just putting MakeValid() back in the original conversion script.

Enter my table of numbers so that I can easily handle each polygon separately, I dumped the polygons to a separate table:

    n.num, w.iso2
    ,w.geom.STAsText() as geomtext
    ,w.geom.MakeValid().STNumGeometries() as numpolygons
    ,w.geom.MakeValid().STGeometryN(n.num).STAsText() as polygon
into dbo.worldpolygons
from WorldBorders w
   master.dbo.nums n
        on n.num <= w.geom.MakeValid().STNumGeometries()
where w.geog.STAsText() = 'POINT (0 0)'

Converting these polygons to geography separately still gives a few errors, but I'm feeling a lot closer. More on this in another post, when I have the time to be able to look at it some more.

The Pope and sneezing

I flew into Sydney recently, around the time the Pope was leaving after World Youth Day. I figured if I saw him, I could go and stand near him and sneeze. (Don't ask me how I'd make myself sneeze – I hadn't worked out that part of the plan.)

Hopefully, he'd reply in the standard way, and I'd have a great story about getting a blessing from the Pope. But then I thought – hang on, the current bloke is German. He'd probably say "Gesundheit" instead, which doesn't have the same ring to it. I'd be stood there, complaining at the Pope, telling him that he should've said "Bless You" instead of "Gesundheit". At least right up until I got carried away by his security.

I'm also thinking that I missed out on a great opportunity in a lift earlier in the year. I must remember to have the sniffles next time I bump into one of the great religious leaders of our time in an enclosed space.

Time to try some exams? Second shot is back

I always tell people to do exams during Second Shot season.

Reason being – it makes the first one a practice. Don't bother studying, don't bother stressing, just go and see how you do. Then you can sit it a second time for real.

Feel like giving it a go? Register at before booking.

Looking for more? Well, the SQL 2008 exams won't be around for a while, but there are a lot of other options out there.

Ctrl+1 in SSMS for sp_who, plus more

I did this by accident, but turns out it's a really useful feature. I was just trying to zoom in on something using Zoomit, that really useful thing that I use whenever I'm presenting these days. But I didn't have Zoomit running, and so SQL Server Management Studio ran sp_who.

And it's not even new. This is an old Query Analyzer thing – I just hadn't come across it before. And it's extensible! Go to Tools, Options, Environment, Keyboard. By default, sp_who is Ctrl+1, sp_lock is Ctrl+2 and sp_help is Alt+F1 (which runs on whatever you have highlighted). Then pick one of the ones that you're allowed to set for yourself, and put a command in. I can't believe I haven't stumbled across it before. I've already put sp_helpindex on Ctrl+3, and sp_helptext on Ctrl+4.

The only complaint I have about it is that if you select a two-part object name and hit Alt+F1 (the shortcut for sp_help), it fails because there aren't quotes around the name. I'd like to be able to wrap stuff up, to make it do "sp_help '*'", where the star refers to the selected text. I thought this would be worthy of posting to Connect, but Michael Swart has already posted something similar. No-one's voted on this yet, but I think it could be really useful (so please, go vote. Put comments in, all kinds of stuff). Great to be able to call sp_helpindex when highlighting a table (I've just added this one), but if this breaks whenever I need to specify a schema, then it's just a little less useful. I'd even like to be able to have something which runs a whole query, using my highlighted text somewhere in there.

At the moment I'm playing around with having a keyboard shortcut for:

select * from sys.dm_db_index_usage_stats

and then highlighting:

where object_id = object_id('sales.salesorderheader')

What do you have on your keyboard shortcuts?

Busy times recently

Time seems short at the moment. I'm putting the finishing touches on a presentation for TechEd Australia 2008. I'm not on the list of speakers at the moment, but I will be when they're next updated (there are a few blanks still). I'm giving a talk about T-SQL techniques, which should be a lot of fun. There was a similar talk given in the US this year, but this is very much my own. I'm going to be following a similar format as the US talk, but there's remarkably little overlap in the two talks.

The US talk seemed very good. It was called "SQL Tricks: Insights from Microsoft IT", but I'm not so big on tricks. Sure, I know plenty, but my talk is going to be focussed more on techniques that I use when writing or fixing T-SQL. Ok, some may be tricks, but the idea is to get you seeing some of the power in T-SQL. The talk will be called "Improving Your T-SQL Arsenal", which points to the weaponry you have in T-SQL, but also gives a nod to my North London friends.

Most of the things in the talk are things that are just as relevant to SQL 2005 as SQL 2008, which seems odd to mention today, since SQL Server 2008 RTM has become available in the last 24 hours. When I reboot my laptop, the installation will be complete and I will be checking that all my scripts still work. This will include my entry in the Demos Happen Here competition.

The DHH competition is not something I'm expecting to win, but I have an entry in nonetheless – a demonstration of the Resource Governor. I think the Gov is a great feature to demonstrate, and I hope the judges see it this way too. But I also think a lot of Dave Gardiner's demo, and I actually hope he wins the SA final next week.

It's been a busy month – my todo list is being stretched rather than shrunk – and I'm not sure when it's due to ease off. I was asked to be in the US next week helping put the new SQL 2008 exams together, but my schedule is just too full. I haven't even had time to look at the betas, although I was pleased to see that a blog has formed to announce beta exams. And of course, TechEd Australia is less than four weeks away. I'll be assisting in the Hands On Labs area as well as presenting. I also want to be spending as much time as I can around the UNICEF project, which I think gives me about 20 seconds for toilet breaks. If anyone knows a way to put an extra several hours into a day (without inducing sleep deprivation), please let me know.

Filtering results without losing the OUTER JOIN

This is a question I seem to answer for people quite often, so I thought I'd write a blog post about it.

Suppose you're joining between two tables (or more, but we'll only consider two to keep things simple) in the AdventureWorks sample database on SQL Server 2005.

FROM Production.ProductSubcategory s
  Production.Product p
    ON p.ProductSubcategoryID = s.ProductSubcategoryID;

I'm doing a LEFT JOIN, because in my results, I want to make sure that I have all the subcategories listed. For fun, you might want to insert a subcategory into Production.ProductSubcategory, so that this query returns a NULL Product row for you – try INSERT Production.ProductSubcategory (Name, ProductCategoryID) VALUES (1,'Empty Subcategory');

But suppose we only want to consider Products that have ListPrice > $1000. Because this doesn't feel like part of the join context, the obvious place to put it is the WHERE clause, right? Well, only if we want to filter out the empty subcategory, and any others that only have cheap items.

Instead, it belongs in the ON clause, like this:

FROM Production.ProductSubcategory s
  Production.Product p
    ON p.ProductSubcategoryID = s.ProductSubcategoryID
    AND p.ListPrice > 1000

I know it doesn't seem like a join condition, because it's only involves one of the tables in the join, but with outer joins we need to make it very clear where the join ends (and thus the non-matches are put back in) compared to where the filtering is done. If you don't want to filter out the subcategories, you can't be filtering in the WHERE clause. It is part of the join condition, because we only consider a match if the product is in the subcategory AND the product is expensive.

UNICEF Australia project at TechEd

It was unfortunate news when I heard that the UNICEF Australia site was hacked a little while ago. An old colleague of mine called me to let me know, and to ask if I knew anyone who could help them out. I used to work at a hosting company with this guy, and had seen quite a few websites get hacked one way or another (the typical methods being SQL Injection or Cookie Poisoning).

UNICEF is one of my favourite charities. They work to promote and protect the rights of children all over the world. As a kid I remember a book called "I Like This Poem" on my mother's bookshelf – a UNICEF publication containing poems that were nominated by children (like Alfred Noyes' "When Daddy Fell Into The Pond") that I can't seem to find any reference of online. So I was more than happy to help them get back online. I got in touch with their hosting provider, who could not have done anything to prevent the attack but were very helpful in trying to help resolve the problem. I also contacted the MVP community and got some help from a few friends in fixing up code.

They got back online, and I hope are relatively secure, but I also had a chat to some people at Microsoft about what they could do, and they've come to the party!

A couple of years ago, there was a community project at TechEd Australia to help the Smith Family (in the DevGarten). This year, UNICEF are getting helped.

If you're going to be at TechEd Australia this year, please try to find some time to get involved in the UNICEF project. The idea will be to make sure that they have a great new (secure) website, that will encourage people to visit, donate, find out what's happening with this great charity. I'm sure everyone who donates some time will learn a lot from the experience, and also enjoy the chance to work for the children of the world.