Hi Tracy,

AFAIK, changing compatibility level does not affect existing data. I did migration from SQL 2000 to 2005 a while ago keeping compatibility level 2000 and eventually changing it to 2005. I did not notice any problem after doing that.

>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)