Sql-server – Will certain Database Compatibility cause performance degradation

compatibility-levelperformancesql server

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?

Best Answer

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.