Question: I'm looking mainly at things like new SQL syntax, new kinds of locking, new capabilities etc. Not so much in the surrounding services like data warehousing and reports...

Answer: 
* New separate types for Date and Time, instead of just Datetime
* New geographic types for lattitude/longitude
* Change Data Capture is pretty neat if you're doing anything where auditing is important
* Configuration Servers, for maintaining multiple databases.

That's what caught my attention at the Heroes Happen Here launch back in April.
HotAdd CPU. <http://msdn.microsoft.com/en-us/library/bb964703.aspx>