Category Archives: 6161

High ROI items for SQL Server 2008

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

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

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

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

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

You can get it from

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:

The problem with America – spatial data

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;
DECLARE @region geography;
SET @region = geography::STGeomFromText(‘POLYGON((-67 49, -125 49, -125 24, -67 24, -67 49))’, 4326);

SELECT  [AddressLine1]
       ,[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!

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

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

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

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.