In addition to learning how to screw up database schema manipulation in SQL 2005, I also picked up a few nifty tidbits in today's ISV event (all subject to change before RTM, and always the chance I misunderstood the material presented):

1. SQL 2005 supports synonyms. Well, it always supported them within a query, but now it supports them as permanent object aliases too.
2. DDL triggers. You can setup triggers for most DDL operations, such as CREATE TABLE, DROP PROCEDURE, ALTER LOGIN, etc.
3. "text" and related BLOB types are being depricated. "varchar" now supports unlimited size, which is defined using the MAX keyword. In other words, use "varchar(MAX)" instead of "text".
4. DML statements now support an OUTPUT clause where you can dump the contents of the "inserted" and/or "deleted" special virtual tables out to a table-valued variable.
5. Native encryption and signing support. Built-in functions that accept a text value and a passphrase (symmetric encrypt) or x509 certificates (asymm encrypt). SQL Server can also store certs internally for these purposes.
6. XML support is pretty cool, but jeez using .insert() or .update() can get really nasty in a hurry if the XPath strings get complex.
7. Kinda neet that you can create a CONSTRAINT based on an XPath expression. You can also create an index based on an XPath expression.
8. SQL Notification Services shows some promise. But aside from old-school client/server scenarios, the SqlDependency class seems to be of limited usefulness. Not very viable for smart-client or n-tier deployments without a lot of extra plumbing code (CAO's or sponsored SAO's and such). However, if you ARE doing client/server, SqlDependency is a nice way to watch for changed query results.
9. Service Broker is rather pimp. It's a message queueing engine that operates within SQL Server. It's contract/schema based, and lets you enqueue/dequeue messages within DML transactions. New MESSAGE, CONTRACT, QUEUE, and SERVICE objects in T-SQL are used to support this.
10. CLR integration looks pretty useful (as long as applied correctly). UDT's and Aggregate UDF's look most promising here.
11. You can use Service Broker to perform long-running transactions, as long as the requester does not need to be notified of completion/failure, or you can use Notification Services to send a message back to them.
12. Microsoft decided to not supply Query Analyzer tool with SQL 2005. Bad move IMO. QA was nice because it was very lightweight, and it also didnt give developers a whole lot of chance to screw up with the "right-click database drop" stuff.
13. ADO.NET 2.0 has a few new handy features...
1. SNAC - Sql Native Client, no longer need MDAC. I am guessing this is a full native .NET implementation of SqlClient, but not clear on that point.
2. MARS - Multiple Active Result Sets. You can essentially have more than one DataReader open on a single connection (useful when doing nested loops) (of course, nested loops are probably bad in your middle tier anyways, but who am *I* to judge this feature?).
3. New Async versions of operations. BeginConnect()/EndConnect(), BeginExecute*()/EndExecute*(), etc.
4. You can now load a DataTable directly from an existing DataReader. No longer need a DataAdapter... MyTable.Load(MyReader).
5. New DataTableReader is an IDataReader implementation that sits on top of a DataTable. MyReader = MyTable.GetReader().
6. In order to enable MARS and async operations, you need to specify "Asynchronous Processing=true" in your connection string. This might end up unnecessary when SQL 2005 is released.