How to check sql script compatibility agains SQL 2008

Question

I have installed SQL 2012 and I must create a application compatibility with SQL 2008. Is any way to check that all scripts that I write are compatibility with SQL 2008 without installing SQL 2008?

Answer 1

I believe that there are two thing that you will need to do to ensure that your app is compatible with SQL Server 2008.

First, the easiest way that I have found to find issues caused by deprecation between versions is to use the sql server profiler tool.

http://www.mssqltips.com/sqlservertip/1370/identifying-deprecated-sql-server-code-with-profiler/ is an excellent introduction into that.

Second, you need to change the compatibility level of the database to the level of SQL Server 2008.

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 } 

So for you that would be ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100;

Some things to keep in mind. This will not change the underlying way that sql server executes the query internally, new enhancements to the query optimizer or algebrizer will not be excluded because of the compatibility level. From what I understand this functionality mainly disables language constructs that were not introduced in the version that you are interested in. That being said, it shouldn't make any difference in what you are trying to accomplish.