I researched this previously, but it's been months and I'm hoping someone has a handy link of reliable information. Given the scenario:

1) currently have sql server 2000
2) upgrade to sql 2005
3) sql 2000 compatibility mode is turned on

months go by inserting and updating data

4) what happens to existing tables and data when you turn OFF 2000 compatibility (to take advantage of varchar(max) and other stuff?

Is there a reliable list somewhere? any drops and recreation of columns? any butchered or loss of data? How reliable is the SQL Upgrade Advisor ?

What about sp_dbcmptlevel (Transact-SQL) for 2005 and ALTER DATABASE Compatibility Level (Transact-SQL) for 2008?

All of this assumes very large existing tables/databases from years of data storage...with stored procs and indices, etc...

Has anyone actually done this? (Turned off sql2000 compatibility on a database with data)