November 20, 2010 at 8:56 am
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)
.·*´¨)
.·`TCH
(..·*
November 20, 2010 at 9:45 am
Nothing will happen to your data or tables - they are already upgraded to 2005 regardless of the compatibility level.
The only thing that will happen is that deprecated commands that are available in 2000 compatibility mode will no longer be available. A good example is the *= and =* join syntax does not work in 2005 compatibility mode.
If you have addressed all of the coding issues - you won't have any problems changing the compatibility level.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 20, 2010 at 10:20 am
tcholzer (11/20/2010)
4) what happens to existing tables and data when you turn OFF 2000 compatibility (to take advantage of varchar(max) and other stuff?
Absolutely nothing.
Has anyone actually done this? (Turned off sql2000 compatibility on a database with data)
Sure. I have a testing DB that I frequently change compat mode on. 2008 to 2000, to 2005, back to 2008, etc.
The upgrade of the tables is done when the database is first started on the 2005 instance. That's during the SQL upgrade or when you restore/attach the DB to SQL 2005. That's the point where the system tables are changed, the internal database version bumped and any necessary changes to table structures made. That's why a DB attached to SQL 2005 can never be restored to SQL 2000, regardless of the compat mode.
All the compatibility mode does is affect how the query processor interprets and executes certain T-SQL constructs. Nothing else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2010 at 11:05 am
Thanks everyone!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply