Rob Farley : sql server 2008

High ROI items for SQL Server 2008

Rob Farley — Thu, 17 Dec 2009 23:02:00 GMT

To persuade your boss to embrace an upgrade to SQL 2008, you need to know which features have high Return On Investment. They may have seen presentations talking about features like Spatial, or MERGE (and been quite impressed), but they may well have left those presentations thinking about the effort that’s would be involved in rewriting applications to take advantage of these features. It’s all well and good to see your customers on a map, but someone has to make that spatial data appear somewhere.

This post is a callout for features that will benefit you (and your boss) as soon as you do the upgrade (or soon after). And I welcome comments to list other items as well.

Block Computation (in SSAS – which reduces the effort in processing significantly, for no change in the application )
Transparent Data Encryption (in the Database Engine – which makes sure that data at rest is encrypted, with no change in the application)
Backup Compression (which reduces the size of backups, and can be set as the default so that existing backup scripts don’t need to change)
Data Compression (minimal change to turn on compression on tables which will compress nicely)
Filtered Indexes (because how far off is your next index creation, really?)
Auditing & Change Data Tracking (because it’s very easy to turn on and then review the data as you need it)
Export to Word in SSRS (because everyone’s wanted this for so long)
SSRS paging (because SSRS used to get _all_ the data for a report before rendering it – but not in 2008)
Resource Governor (easy to set up, nice to have in place for when you might want it)
Hot-add memory (so that you can just plug in more memory without having to do restarts)

I’m not suggesting that an upgrade should be done flippantly. You should still consider the effort of thoroughly testing your system under SQL 2008. But hopefully this list can highlight some of the things that I’ve found are good persuaders. A list of “What’s New in SQL 2008” can be found at http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

Like I said, you may have other items on your own list, and I invite you to comment on this. You may also have things in place to handle things like encryption, and you may be running Hyperbac or one of the other compression tools.

StreamInsight talk coming up at SQLBits

Rob Farley — Fri, 20 Nov 2009 11:47:24 GMT

My talk on StreamInsight is up next. I’ll try to blog more about that later. For now, I want to mention more about SQLBits itself. This is by far the largest SQL-only conference I’ve attended (I haven’t been to SQL-PASS yet), and it’s great to be involved.

Yesterday I had an all-day seminar about the new items for Developers in SQL 2008. It was a good time – the delegates responded very positively, and many of them have caught up with me since.

But for me, the conference is being a great way of catching up with (and meeting for the first time) a bunch of SQL people that I rarely see. I’ve met people that lived only a few miles from where I grew up, and people that read my blog (Hi!), discovered people who have connections to Adelaide, and even found that my Adelaide friend Martin Cairney (who is also here) has a strange connection to Donald Farmer (of Microsoft), that their parents shared a back fence or something… Now Trevor Dwyer tells me a colleague of his knows me from somewhere… the world is very small here.

My StreamInsight talk will be interesting I hope. I have some stuff to show off, and I plan to involve the audience a little as well. If you’re at SQLBits and feel like being involved in an interactive session, then definitely come along. I want to hear from people in the audience who have dabbled with StreamInsight and also other vendors’ Complex Event Processing offerings. This is a brand new technology from Microsoft, and there will be a large range of adoption levels in the room.

A review – SQL Server 2008 Internals

Rob Farley — Sat, 13 Jun 2009 04:37:45 GMT

I’m reading SQL Server 2008 Internals at the moment. I say ‘reading’, because I think it’s going to be long-term thing. It’s just so full of useful information, that I’m sure I’ll be reading it over and over for a long time yet.

Kalen Delaney’s books are always great, but in this one she has help from Paul Randal & Kimberly Tripp, Conor Cunningham and Adam Machanic – all SQL Server legends in their own right. The book they have made is just excellent, and should be read by everyone who wants to get deeper into SQL Server. There are some sections I’ve only skimmed over so far, whilst others I’ve read thoroughly. In time I think I will have read every page multiple times, but this is definitely a resource that can be read that way (yes, Kimberly, it has a good index).

It covers so much useful stuff it’s hard to think of a better resource for SQL Server 2008. It doesn’t go into design very much, but it will affect your design decisions. It doesn’t go into writing queries, but it will affect the queries you write. I really think this book would be an asset to anyone who wants to know more about SQL Server.

[Updated: This link should take you to where you can find and buy the book. MSPress have 50% off their books until June 30, 2009, which makes this book an even better investment]

Seriously cheap exams in Australia

Rob Farley — Wed, 06 May 2009 02:56:41 GMT

If you’re an MCP in Australia and you haven’t passed any exams over the past couple of years (since July 1, 2007), then Microsoft has an offer at the moment to let you do an exam for only US$25 (until June 30, 2009). Ok, so that means the price really depends on the value of the Aussie dollar, but either way, it’s not a bad opportunity.

The offer is only on for a very short time, but why not check out http://www.learnandcertify.com/mcpupgrade/ and see what you can do? I’m thinking it’s a nice opportunity to knock over one of those Upgrade exams to get yourself from MCITP:SQL2005 to MCITP:SQL2008. You don’t get a second shot with this one, and you can only get one voucher – but it’s so cheap you may as well try it.

SQL Server 2008 SP1 download

Rob Farley — Wed, 08 Apr 2009 01:01:49 GMT

You can get it from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19

For all those people who have been waiting for SP1 before considering a migration to SQL Server 2008, it has now been released.

There’s also a new version of the Feature Pack for SQL Server 2008, available from: http://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

The problem with America - spatial data

Rob Farley — Sun, 16 Nov 2008 21:53:27 GMT

Ok, it's not really a problem with America, just a problem with the perception of straight lines like the USA-Canada border. It's roughly the latitude line 49 degrees north. A line that goes East-West, on a straight line.

Except that latitude lines aren't straight - except the equator. Just look on a globe and you'll see what I mean.The curvature of the earth just gets in the way a bit. If you're ten miles from the North Pole and travel east until you come back on yourself, you've just gone around in a circle that's 20 miles across. The smaller the circle, the more obvious the effect. The bigger the circle (like when you're near the equator), the less obvious the effect. But it's still there.

Pilots understand the principle. When I flew from London to Seattle earlier this year, we went across the top of Greenland, because that's the quickest way of getting there. Pick up a globe of the world, and you'll see exactly why we should fly that way.

When someone wants to fly from one point on the 49th parallel to another, or to simply draw a line, the shortest route actually goes a little further north. The 49th parallel is like that 20-mile circle - it bends to match the curvature of the Earth.

So if you're mapping the world using spatial data for SQL Server 2008, you may want to consider this when looking at the American border. It's not a straight line along the 49th parallel, because the 49th parallel isn't a straight line.

To demonstrate this, my friend Greg Larsen (who runs the Olympic Area SQL Server User Group) has sent me a query. You'll notice that there are some locations north of the 49th parallel that are with the polygon which seems to be bound by the 49th parallel.

USE AdventureWorks2008;
GO
DECLARE @region geography;
SET @region = geography::STGeomFromText('POLYGON((-67 49, -125 49, -125 24, -67 24, -67 49))', 4326);
SELECT [AddressLine1]
       ,[City]
       ,[SpatialLocation].Long AS Long
       ,[SpatialLocation].Lat AS Lat
FROM [AdventureWorks2008].[Person].[Address]
WHERE [SpatialLocation].STIntersects(@region) = 1
and [SpatialLocation].Lat > 49;

This query actually gives us 632 rows. That's 632 addresses in the sample database which are more than 49 degrees North, and yet are within a polygon which has corners at -67 49 and -125 49. Hopefully you can look at the picture of the US border and see where those 632 rows much be.

SQL Server spatial stuff is really clever. But perhaps we need a way of telling it to draw lines east-west, instead of as the crow flies.

Missing Index in SQL Server 2008 - should try harder!

Rob Farley — Sun, 12 Oct 2008 03:38:48 GMT

Ok, maybe I'm being a little harsh, but I just feel like it should be better.

Let me show you the niceness of the way that missing indexes are handled in SQL Server 2008.

Using AdventureWorks (not AdventureWorks2008) on a SQL Server 2008 install, if I show the Execution Plan from this simple query, I get a nice suggestion. My query...

select productid, orderqty
from sales.salesorderdetail
where carriertrackingnumber = 'FB88-4B92-82';

...could be improved through better indexing. It uses 1240 reads to get this data, which seems awful. The system shows me that it could be improved, and suggests an index.

It's there, in green. It says:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [Sales].[SalesOrderDetail] ([CarrierTrackingNumber])

(that's right, no semi-colon on the end, but I'm fine about that)

What I'm not fine with is the fact that this index isn't actually so ideal. If I create it (supplying a name, of course), we see it's being used, and it should be clear that a better index ought to be better.

This query uses 42 reads to get the required information, which is significantly better than 1740, but still not brilliant. In fact, 42 is about 2.4% of 1740, so it's hardly the 99.6512% improvement that Management Studio suggested would be seen.

My preference would be to consider that Key Lookup as well. it's taking 92% of this improved query. We can avoid the Key Lookup by creating an index which INCLUDEs the columns we're interested in. Like this:

CREATE NONCLUSTERED INDEX [MyNewIndex2] ON [Sales].[SalesOrderDetail] ([CarrierTrackingNumber]) INCLUDE (ProductID, OrderQty);

If I create this index, we see that the execution plan becomes just the Index Seek (on my new index), and the number of reads drops to just 3. Yes 3. That's 0.17% of the original query, and only 7.1% of the reads of the improved query! 99.83% of the original reads have been eliminated - much more like the figures promised by my Missing Index suggestion, except it got it wrong.

I like the idea of detecting Missing Indexes, and I love the fact that it suggests these in Execution Plan viewer... I just want it to be slightly better by considering INCLUDEd columns.

I've suggested this be improved on the Connect site at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=375024

Accelerated SQL Server 2008 - Walters, Coles, Ferracchiati, Rae & Farmer

Rob Farley — Sun, 21 Sep 2008 09:03:23 GMT

Apress asked me to review this book, so here goes... I was a little disappointed, but on the whole thought it was pretty good. I also posted this review to Amazon, where I figure more people will read it.

I saw this book come out, and had high expectations. The authors are all great guys who really know their stuff. What's more, Donald Farmer is _the_ guy for Data Mining, so I was hoping for a big section about the improvements in that area for SQL 2008.

Looking through it though, it feels like a book that was written for SQL 2005 that just got updated a little for SQL 2008, and that wasn't what I was wanting. And to boot, there are things that are just plain missing. There's not a single page on Data Mining, and some of my favourite features just don't appear. For Xmpl, in the FOR XML section, there's no mention of FOR XML PATH at all - which has to be the best way of using FOR XML.

However, I will still recommend this book. I will recommend this book to people who are moving from SQL 2000 and want to know what's new in both SQL 2005 and 2008, but there is a better reason again:

It's really honest! It's written by Microsoft guys, and they give really frank opinions about things. There are times when they happily point out that one feature or another isn't really as useful as it sounds. It's the kind of thing that more books need.

Another new SSMS 2008 goodie - moving files into a project

Rob Farley — Wed, 27 Aug 2008 21:24:41 GMT

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)

Rob Farley — Mon, 25 Aug 2008 11:11:49 GMT

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)'

...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:

select
    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
   join
   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.

COPY_ONLY backup option more obvious in SQL Server 2008

Rob Farley — Fri, 20 Jun 2008 07:32:10 GMT

I feel like I keep finding reasons to use SQL Server 2008 Management Studio with SQL 2005 instances, and here's another - the COPY_ONLY option now appears on the Backup dialog box (others include the DROP & CREATE scripting option, multi-server queries and the Select Top 1000 Rows menu entry).

It's not a new option (it just wasn't part of the dialog box), but there's an option when backing up a database called COPY_ONLY. It goes in the WITH section. It means that the backup you're taking won't be included in your ordinary backup/restore sequence. Let's paint a scenario.

Your system takes a full database backup on a Sunday morning. Monday, Wednesday and Friday you do differential backups. Everything's great, each of the differential backups only looks at changes since Sunday, when the last full backup was taken.

Except that one Thursday, one of the developers wants to refresh the test environment with a recent copy of the production system. Instead of finding Sunday's backup and using that, they make a new backup, and restore that onto test. Everything works, and that new backup gets deleted.

Which means that Friday's differential backup is based on the Thursday backup instead of Sunday. When a disaster occurs on Saturday, you're in a bit of strife.

If the developer had used the COPY_ONLY option, then Friday's differential would still've been based on Sunday's backup. That's the whole point of COPY_ONLY - it doesn't reset all the flags that indicate which data should be included in the differential.

Like I said earlier, it's not new - it's was introduced in SQL 2005. I don't know why it didn't appear in the SQL 2005 dialog box - it didn't even turn up in a service-pack. Since so many people learn about features through the user-interface, it really could've helped educate users about this useful option.

"No Match Output" in SSIS 2008

Rob Farley — Thu, 12 Jun 2008 03:50:21 GMT

The hairy Irishman (his description - I just call him Sacha) is presenting to the Adelaide SQL Server User Group today, talking about improvements in SQL Server 2008 Integration Services. Right now he's talking about the No Match output of the Lookup Component, and I'm wondering why I had always just accepted that in SQL 2005, rows that didn't match a lookup would get pumped out to the error output.

For those of you who are saying "Sorry, what?", let me explain...

If you have a data flow in Integration Services which is missing a particular piece of information, and you need to get that information from another source, you are likely to do a Lookup transformation, which can perform a query to find the values to be inserted into each row in the flow. Of course if the data is just in two tables, you'd just do a join in your original source query, but as soon as you're talking about files, you don't have that luxury in quite the same way. Sure you could populate a table and then read it back, but a Lookup may end up being much quicker.

Now, in SQL Server 2005, if a row couldn't find the looked up value, it would be considered an error, along with truncation errors and errors in connection to the lookup source. I just accepted this as okay before, but the more I come across the No Match output (which can be used as well as the error output), the more I like it. So I can much more easily separate the rows that have caused errors from the rows that just couldn't be found in the lookup source.

PowerShell changes in SQL Server 2008 RC0

Rob Farley — Thu, 12 Jun 2008 02:48:47 GMT

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular PowerShell window. Except that if you've just installed SQL Server 2008 RC0, you may have noticed it doesn't work any more.

Never fear, they've just changed the name a bit. They've put 100 on the end of both. So just change your console file to list the two snapins as SqlServerProviderSnapin100 and SqlServerCmdletSnapin100. Calling PowerShell with the -PSConsoleFile option still works as before of course.

On a lighter note, they've fixed the case sensitivity. I can now "cd databases" just fine, without it needing me to use a capital D.

Fuzzy in T-SQL

Rob Farley — Thu, 17 Apr 2008 14:49:49 GMT

SQL Server gives you Fuzzy Lookups and Fuzzy Grouping, but only in SQL Server Integration Services. It's not even on the list of SSIS enhancements for SQL Server 2008.

This week at the MVP Summit, I was having a discussion about this with Jamie Thomson, and we had a chat with one of the T-SQL guys to suggest it. The response came back with "Log in on connect", which I did just now. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338664

Please check it out and provide comments, votes, validations. All this will help persuade Microsoft to implement this useful feature. And tell your friends too! The more it receives, the more likely it will be implemented.

Local Server Group queries

Rob Farley — Tue, 04 Mar 2008 01:54:19 GMT

This is really quite cool, especially since it's a tool feature, and one that will help SQL Server 2005 installs.

Suppose you have many servers, all registered in Management Studio, in the Registered Servers window. Now you can right-click on the Local Server Group, and pick the "New Query" option. Write a query, and it gives you a result set including the results from all the servers, as if you'd done a UNION ALL using linked servers.

So now if you want to do something like

select name, database_id from master.sys.databases;

on a query against multiple servers, you get three columns. Server Name, plus the columns from the query.

SQL Server 2008 PowerShell SnapIn

Rob Farley — Mon, 03 Mar 2008 23:17:32 GMT

Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).

But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it's not a SnapIn for PowerShell, but it turns out it is. We ran:

Get-PSSnapin -reg

...and got the following results:

Name        : SqlServerCmdletSnapin
PSVersion   : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name        : SqlServerProviderSnapin
PSVersion   : 1.0
Description : SQL Server Provider

So then I ran:

Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin

And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.

<?xml version="1.0" encoding="utf-8"?>
<PSConsoleFile ConsoleSchemaVersion="1.0">
<PSVersion>1.0</PSVersion>
<PSSnapIns>
<PSSnapIn Name="SqlServerProviderSnapin" />
<PSSnapIn Name="SqlServerCmdletSnapin" />
</PSSnapIns>
</PSConsoleFile>

I'm still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it's worth having it as an option.

Using ScriptingOptions with SQLPS

Rob Farley — Mon, 03 Mar 2008 22:53:47 GMT

I've written before that SQL Server 2008 (February CTP) gives you SQLPS - a PowerShell interface to SQL Server.

So I was trying to get the ScriptingOptions happening, and this is the only way I've found so far. It's ugly, and if you have a better way of doing this, please post a comment for me.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.DriAllConstraints = $true
(get-item Sales.Store).Script($so)

And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.

Melbourne Launch

Rob Farley — Mon, 03 Mar 2008 21:50:57 GMT

So I'm at the Heroes launch in Melbourne today. I'm going to hang out at the Experts booth, so if you're here and have a question about SQL Server 2008, why not come over?

SQL Server 2008 CTP6 gives you PowerShell

Rob Farley — Sat, 23 Feb 2008 06:00:12 GMT

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

Basically, you can now open up PowerShell, and change directory to the PowerShell drive "SQL:". Then change directories through the instances, databases, tables, and so on. At any point, try something like "dir | gm" (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.

Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO - when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions - I found this a little disappointing, and I haven't figured out the best way of getting help on methods that are on these objects either.

It's definitely a good start though - should be a very nice feature of SQL Server 2008.

Querying distances using Spatial data types in SQL 2008

Rob Farley — Tue, 08 Jan 2008 12:34:00 GMT

SQL Server 2008 has spatial types which are very useful for all kinds of applications. I thought it would be nice to produce one of those charts like you get in maps, telling you the distance between cities.

So I start by populating the data. This uses the geography data type, which understands about the spherical nature of the world. The data is populated using POINTS, using data which I found around the internet.

create table dbo.Cities
(id int identity(1,1) primary key,
name char(3) not null,
fullname varchar(100) not null,
location geography);

insert into dbo.cities values
('ADL','Adelaide', 'POINT(-34.92 138.58)'),
('MEL','Melbourne', 'POINT(-37.78 144.97)'),
('SYD','Sydney', 'POINT(-34 151)'),
('PER','Perth', 'POINT(-31.95 115.87)'),
('BRI','Brisbane', 'POINT(-27.48 153.13)'),
('CAN','Canberra', 'POINT(-35.3 149.12)'),
('HOB','Hobart', 'POINT(-42.87 147.32)')
;

Now that this is populated, a simple query will produce the results that can be put into a report. STDistance is a method which applies to one geography value, accepting another as a parameter.

select c1.name city1, c2.name city2, c1.location.STDistance(c2.location) / 1000 distance
from dbo.Cities c1
join dbo.Cities c2
on c2.id <= c1.id
;

Now, I can make a simple matrix in SSRS, using =IIF(Fields!city1.Value = Fields!city2.Value, Fields!city1.Value, Sum(Fields!distance.Value)) as the value in the centre of the matrix, with city1 and city2 on the rows and columns. This formula means that through the main diagonal of the matrix, the city names will be displayed. Hiding the header rows and columns, we quite quickly achieve something that looks like the image here. Of course, if you're doing this for a proper application instead of just a proof-of-concept, you should take the time to format it more nicely. Colours, borders, that type of thing. But you get the idea.

I'm not sure how useful this is to your own applications - but it's so easy to query this kind of spatial data, I encourage you to start considering it to be a useful part of your toolset.

This week, a tour of SQL Server User Groups in Australia starts, covering the spatial features in SQL Server 2008. The list of the meetings are at http://www.sqlserver.org.au, and includes all seven groups.