3

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?

|improve this question
5

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.

|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
  • That's not strictly true - it will also change the outcome of the same syntax in some cases (see docs.microsoft.com/en-us/sql/t-sql/statements/…) – Mark Jan 10 '18 at 17:38
  • Yes, a lot has changed in the 4 years since I wrote that. For older versions this is true, the compatibility level only impacts the supported level of T-SQL. In newer versions of SQL Server the compatibility level also changes the estimator which can have an impact on query execution. – mrdenny Jan 11 '18 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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