Table compatability level in SQL 2008

Posted on 2011-10-22
Medium Priority
Last Modified: 2012-05-12
I have a database that is running compatibility level 90 in a SQL 2008 Instance on my server and I what to convert it to level 100 (SQL 2008) so I can add some new tables that have a "DATE" as well as a "TIME data type.  Once I change the compatibility level in the SQL Management Studio can I revert back to level 90 if the change breaks my ODBC based application?
Question by:chasmx1
LVL 25

Expert Comment

ID: 37013750
Introducting 2008-features will compromise the 90-compatibility. Without 2008-features changing back is no problem.
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 37013886
No, not if you are adding DATE and TIME datatypes, of course if you remove those newer features it should be OK.

But, take the time to create a test database and try it out in a development / QA environment before you begin.

So, restore your database to "test" upgrade compatability mode, then connect your ODBC based application to "test" and start testing.

It is not just the compatability mode per se, it is how the engine behaves like the differences in language settings and the smalldatetime, fulltext search, some of the XML functions, then little things like "mydate + 1" if mydate is one of the newer date / datetime datatypes.

Then there are more differences in some of the add-ons like SSRS, SSAS and so on.

So, it probably isnt compatability mode as such, just everything else that needs testing :)

Have a look at : http://msdn.microsoft.com/en-us/library/bb510680.aspx for some of the differences.

But the app running 2005 syntax should have no real problems from an ODBC perspective. The 2008 Native Driver (ODBC) is compatable with the earlier versions. You can read about that on : http://msdn.microsoft.com/en-us/library/ms131415.aspx

Oh, and if upgrading, then really should do the full backup and update statistics / rebuild indexes, and your stored procedures will likely recompile because of the change to compatability mode, so, best do the stats first (unless simply testing syntax).
LVL 70

Expert Comment

by:Éric Moreau
ID: 37014007
>>Once I change the compatibility level in the SQL Management Studio can I revert back to level 90 if the change breaks my ODBC based application?

You could but you will lose all the 10 level feature (like date and time)
LVL 50

Expert Comment

ID: 37015356
you could also try implemeting views to "hide" the new datatypes to from old applications...

and instead of triggers on the tables ...
LVL 51

Expert Comment

by:Mark Wills
ID: 37015496
Hmmm... Just read the question again...

Sounds like your application is already talking to SQL Server 2008. You dont mention what ODBC drivers you are using and I previously assumed SQL native drivers.

But, so far... it means that you can currently talk to a SQL Server 2008 instance, so, points more towards supporting syntax specific and "new features" when changing from compatability mode 90 to 100.

So, as a first step, just change compatability mode without adding any new features at all. Because you will need to test this in isolation, then do the backups first, upgrade, test and if needed, then restore. But if the initial tests appear to be OK, then you can try with the new features. Again this would need to be done in isolation without your normal users online.

Is it your own application ? Do you have source code ? Can it be changed easily enough ?

If yes, then why not try extracting the queries and test them independantly for syntax ? Or, following on from Lowfatspread's suggestion, create a view to cast currently known and working data as DATE and TIME and (using the view instead of the table in your code) see if you get the same results.

I dont think you should have any problems, but then it is very dependant on what the SQL is actually doing, and the ODBC driver you are using (and if you can change any of the application).

As mentioned above if you do things like "mydate + 1" and mydate is destined to become one of the newer date datatypes, then it can fail (use dateadd() instead), and, if you have the same column name then 2005 is OK but 2008 will break with "ambiguous column" type messages.

So, step at a time, test in isolation, use backups as the ultimate "safety" because you will need to test in isolation anyway.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question