1. ADO.NET v2 will allow stored procs in process access to SQL data
2. .NET language based stored procs..I am sure you already knew that..:-)
3. SQL and XQueries can be intermixed - that is quite an interesting stuff
4. Yukon by default allows access to data via HTTP/SOAP. So access to data via web services. You need to think about security implications before you start using this though.
5. A lot of new operators - PIVOT, UNPIVOT, APPLY etc.
6. Statement level recompiles in relational stored procs
7. Queuing primitives like WAITFOR etc. You can use this to say that you want to wait till this particular updated is done before you want to continue.
8. Query Notification - This is a real good feature as it makes finding dirty caches much easier. So now SQL Server will notify registered listners when data in tables change etc. Much better that polling or doing the trigger -> Extended Stored proc kind of stuff