Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running a variety of SQL Servers from 2005 to 2012 across several machines. I have many of these databases running in SQL Server 2000 compatibility mode.

Will using an older compatibility in a newer version of SQL Server cause any performance issues?

What is the different between these databases, other than older versions of SQL Server being able to access them?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

All the compatibility level does is tell SQL Server to use the older version of the T-SQL language that was available in that version. For example if within a SQL 2008 R2 server with the database running in SQL 2000 compatibility mode if you try and access some of the new syntax it won't work.

For example

select *
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'limited')

will throw an error in a SQL 2000 compatibility mode database.

As for query execution, that won't be changed by the compatibility mode.

share|improve this answer
    
and this means no performance difference at all? It's simply a client end difference? The language I'm allowed to use when writing an sql statement can be older? –  meltdownmonk Jun 20 '13 at 12:49
    
It's simply a language difference, nothing more. You can use the same drives on the client to connect to any database on the instance. When you change the compatibility level the code which is run within the query engine stays the same. There's no branching logic in there for executing queries. –  mrdenny Jun 20 '13 at 16:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.