question

Ian Roke avatar image
Ian Roke asked

Is it possible to simulate older versions of SQL Server without having them installed?

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?

sql-server-2008sql-server-2005t-sqlsql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

You can change compatibility level of each individual database.

See ..

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ha - you learn something new every day! +1
2 Likes 2 ·
Thomas 1 avatar image
Thomas 1 answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I disagree. You're not taking into account deprecation and removal of features.
0 Likes 0 ·
venkatreddy avatar image
venkatreddy answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.