I ran the SQL Server 2005 Upgrade Advisor against a test database that I am currently working with. I'd say it's a pretty typical SQL Server 2000 database: lots of complicated stored procedures, user-defined functions, triggers, etc. Here are the issues that the upgrade advisor reported:
- Remove references to undocumented system tables
- Outer join operators *= and =* are not supported in 90 compatiblilty mode
- Non-integer constants are not allowed in the ORDER BY clause in 90 compatibility mode
- Changes to behavior in syslockinfo and sp_lock
- Other Database Engine upgrade issues
Obviously, #5 bears looking into more. #2 is the result of that old outer join syntax that I learned when I was working with Sybase way back when. [I guess I'm really going to have to commit to using the OUTER JOIN clause ...]
None of these issues would prevent the database from being upgraded, or cause the upgrade to fail. However, they would definitely need to be addressed before going to production (particularly #2 and #3) ...