There are a bunch of features in SQL 2008 that have been on the ask list for a VERY long time. I suspect DATE and TIME types will win the prize as I remember asking for these as a customer of 6.5 10 years ago. However one that's been around for a long time (I remember hearing it asked for as part of 7.0) is the spatial data type support. Given the prevalence of GPS data online, geo services (http://maps.live.com etc) and location based services the time seems right to move this capability much more into the mainstream. Its been a long journey I first remember hearing about GIS back in 92 when a colleague was involved in GIS startup project in the local government org that we worked at. I was especially jealous at the time as they had 2 color SPARC stations (the only SPARCS in the org period) and we had to make do with our 80286s :-)
In the intervening years companies like ESRI have made great strides in moving the adoption of these technologies forward, it will be interesting to see how putting them into the core of SQL Server changes this landscape as there have been some very impressive solutions built using SQL Server 2000/2005 and partners over the last few years.
All right, before I started playing with this myself I decided to do a little research, here is what I came up with;
Web Cast by 2 PMs working on the project.
Blog of Isaac, one of the 2 PMs from the Web Cast
MSDN Forum for Spatial Support
VERY high level data sheet
However the best source of info I found was good ol Roger Doherty (a man I have almost never forgiven me for huge amounts of pain and late nights he caused me when was the ISV Evangelist for BI and I was a Dev Mgr for BI start up...) who is starting the SQL2008 ISV evangelism train as we speak. In the time I have known him roger has spent most of his time as an evangelist but he also worked as a dev on a feature we shipped in Server 2003, so he has a good technical background. His demo app uses the new Geo types along with FILESTREAM in what I would imagine is going to be a very typical app type.
This blog entry has a WEALTH of information that I have so far found invaluable.
The only disappointing thing I have found so far (and I am still researching ) is that it appears that SQL2008 uses flat-earth geometry and not curved earth. The app I am trying to build uses Great Circle Lines, which means I might be busted for now.
UPDATE: Isaac says there is support for round earth in Geography, looks like I have a fun weekend ahead.