Service Pack

A tiny bit behind-the-times with this news item because of holidays in between, but it's worth mentioning that the first service pack for SQL Server 2008 is now available.

There's a useful article on the MSDN blogs about how to go about creating a slipstreamed SP1 install, so that you can install a complete SP1 build in one go on a new server rather than having to install the RTM build and then running the service pack over it. I haven't tried this myself yet, but if it works this is going to be a huge convenience and a real time-saver.

Interesting developments

The Windows 7 Release Candidate build was made available to developers today, with a public release following next week. This surely means that a Release-to-Manufacturing build is only a couple of months away, despite Microsoft's official shipping projection of "early 2010".

I'll be evaluating the build myself in the coming weeks and am curious to see how it differs from the beta. The most interesting addition to the RC build was revealed a couple of days ago - XP Mode, a fully virtualized Windows XP environment running within Windows 7. Assuming this works as advertised, this should be the killer feature that will finally prompt businesses and enterprises that have up to now not upgraded to Vista to make the jump. It's far less of a leap of faith to move to a new OS if you know that, at very worst, you'll still be able to run certain contrary applications in native-XP mode thereby preserving full backwards compatibility.

It's a really smart move on Microsoft's part. For this and other reasons, I think that Windows 7 is going to be an enormous, Windows 95-size, hit. I'll post more here once I get a good look at the RC build.

A New Frontier

Tiengemeten is a small island located in part of the Maas delta about 20km south of Rotterdam. It's a nature reserve these days, the last inhabitant having left the island about two years ago. It's a beautiful place, wild and open, big enough to spend a day exploring and small enough to get around on foot. It's well worth a visit if you're in the neighbourhood.

It also makes for a suitable case study in presenting some of the spatial features of SQL Server 2008. I'm planning a series of posts on this topic, so this won't be the last mention of Tiengemeten on this blog.

Geographic features can be represented in several different ways using the spatial data types in SQL 2008; as points (longitude & latitude coordinates), lines (a joined series of coordinates with a begin and end) or polygons (two-dimensional closed areas bound by a set of coordinates). Polygons, broadly speaking, can be any shape and size. You can define Asia as a polygon, for instance, but also go to the other extreme: a mapping vendor I spoke to last week has polygons defined for every group of trees in the Netherlands!

I've got a SQL 2008 database table with 27 rows, each with one column of datatype geography which in turn contains one polygon per record. There's one large polygon with over 1100 data points representing the Dutch mainland:

..and 26 more records containing the polygons for each of the Dutch islands of any size:

Putting them all together, a simple SELECT [GeoPolygon] FROM NLIslands; gives us a very recognizable set of shapes in the Management Studio:

Tiengemeten is the polygon that looks like this:

Let's examine the record more closely. The data for the polygon is stored as a very unwelcoming looking hexadecimal binary number, 0xE610000001040C000000000068... and so on for another 400 characters or so. We can produce a more friendly-looking representation of this data by using the ToString() method of the geography datatype, however.

SELECT [GeoPolygon].ToString() FROM NLIslands;

returns

POLYGON ((4.3239999990910292 51.725999999791384, 4.3379999995231628 51.7269999999553, 4.3540000002831221 51.731000000145286, 4.3560000006109476 51.731999999843538, 4.3560000006109476 51.734999999869615, 4.2760000005364418 51.754999999888241, 4.2689999993890524 51.75400000018999, 4.26799999922514 51.751000000163913, 4.2760000005364418 51.74599999981001, 4.296000000089407 51.736000000033528, 4.3220000006258488 51.728000000119209, 4.3239999990910292 51.725999999791384))

...which is instantly recognizable as a set of longitude and latitude co-ordinates. You'll notice that the first and last x-y pairs are identical; this is obligatory for a valid polygon definition.

You can produce the exact same representation yourself by reversing the process.

declare @v_Tiengemeten  geography = 'POLYGON ((4.3239999990910292 51.725999999791384, 4.3379999995231628 51.7269999999553, 4.3540000002831221 51.731000000145286, 4.3560000006109476 51.731999999843538, 4.3560000006109476 51.734999999869615, 4.2760000005364418 51.754999999888241, 4.2689999993890524 51.75400000018999, 4.26799999922514 51.751000000163913, 4.2760000005364418 51.74599999981001, 4.296000000089407 51.736000000033528, 4.3220000006258488 51.728000000119209, 4.3239999990910292 51.725999999791384))';

select @v_Tiengemeten;

Not very exciting so far for sure, but we're only getting started here. If you'll bear with me for a couple of months, I should be able to demonstrate some of the rich geospatial functionality of SQL Server 2008 and in particular how spatial data can be combined with "normal" relational datasets to produce surprising and incredibly useful queries and results.

If you'll permit a pun, watch this space.

Cheat sheets

Or, to be more accurate, handy lists of SQL shortcuts, syntax, functions and other useful reference topics covering a multitude of common situations. And they're all here...

Be careful what you wish for

It was only meant as a joke, obviously, when I posted a few weeks back about web searches for U2's 360 tour ending up here, but I checked the site statistics just now and...that posting has the highest number of hits for the entire site.

This suggests a new and devilishly cunning SEO strategy. Repeat after me: U2, U2, Bono, U2, half-good new album, U2...

...and watch the traffic increase.

A little light reading

Two brand new books arrived in the post last week, and I'm really looking forward to diving into both. The first is "Microsoft SQL Server 2008 Internals" by the mighty Kalen Delaney (a good Irish name, if ever I heard one). The book is billed as "the definitive guide to the internals and architecture of the Microsoft SQL Server 2008 relational database engine", so it's obviously essential holiday reading.

The second book is "Inside Microsoft SQL Server 2008: T-SQL Querying", Itzik Ben-Gan's follow-up to the peerless "Inside Microsoft SQL Server 2005: T-SQL Querying". This latter tome and its companion volume "Inside Microsoft SQL Server 2005: T-SQL Programming" are the two best SQL books I've ever read, by some distance. The elegance of Mr. Ben-Gan's queries and his entire approach are nothing short of inspirational.

In the prosaic world of datasets, records, queries and joins I'd go so far as to say that Mr. Ben-Gan is the closest thing we have to an artist, and I can only urge anyone with the least interest in writing better SQL code to read his books and learn from the master. We're not worthy!