I am trying to write some code that I hope will run on SQL Server 2000, 2005, 2008 and beyond. I currently have 2008 R2 on my machine but I would also like to test it will work on older versions of SQL Server but without installing them.
Is this possible or is it just a case of remembering to take out WITH clauses when referencing indexes in the FROM statement and so on?
Changing the compatibility leve is a good way to go (+1 Jonlee) but do be aware that it's not perfect... for example, if you're reading from system tables, then you won't find 100% compatibility. So, in my tools I read the system tables based on the server version that is installed, and not based on the compatibility level. If you're just interested in standard application layer stuff, then the compatibility level change will do you just fine...
The biggest problem with compatibility level that I find is that new features will often still work.
For example, on a SQL 2005 box, with compatibility level set to 80 (for 2000), this query still works, despite using features not available in the older version.
select *, row_number() over (order by name) from sys.objects
Compatibility levels are designed to allow older code to work on newer systems, not to verify whether new code will work on older systems.
The only sure fire method is to develop against the oldest version of SQL that you plan on supporting. I know you said you did not want to install them but at the end of the day, the only way you will know for sure is to actually test against those older versions. You can still use the SQL 2008 workstation tools against a SQL 2000 instance but the only way will be able to safely avoid using a feature that did not exist until a later version is to code against the oldest supported version.
Hi,First you go through the compatability of databases for each version for the sample code that you have,If it is ok,then you need to have check with the system tables.System tables will be differnt for diffrent versions of SQL server.You may face problem in this case.
