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.
Page compressiong sounds really nice to me. Haven't used it yet, though.

<http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx>