The forum responses to Steve Jones' editorial (Why Upgrade?) are mixed, at best. Some are describing why they will or are upgrading, others are descibing why they're waiting. Excellent points (mostly) on both sides of the argument thus far.
Here's a handful of "gotchas" some friends and I have encountered:
1. Security. It's different - a lot different. It can be fun, it can be tricky, but it will definitely be a learning experience.
2. Database diagrams. For now, I'm creating SQL Server 2005 databases from backups of SQL Server 2000 databases. One way to get the database diagrams to display is to navigate to the database properties, click on the Options, and change the database compatibility from SQL Server 2000 (8) to SQL Server 2005 (9).
3. To quote my friend Harper Trow, "in [SQL Server] 2005, you cannot do a select distinct with an order by and not have the column in the select statement". There's an article here...
4. In SQL Server Management Studio, the F7 key is your friend. It displays the summary for the item you've selected in the Object Explorer.