Table compatability level in SQL 2008

Posted on 2011-10-22
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

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

    Accepted Solution

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

    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 69

    Expert Comment

    by:Éric Moreau
    >>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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now