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?

more ▼

asked Mar 05, 2010 at 02:20 PM in Default

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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

more ▼

answered Mar 05, 2010 at 05:24 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

+1 @Matt, good to point out the possible issues and not just answer the question.
Mar 06, 2010 at 03:55 AM sp_lock
(comments are locked)
10|1200 characters needed characters left

You can change compatibility level of each individual database.

See ..

more ▼

answered Mar 05, 2010 at 02:48 PM

sp_lock gravatar image

9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 06, 2010 at 05:49 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Ha - you learn something new every day! +1
Mar 06, 2010 at 08:00 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 06, 2010 at 05:40 PM

Thomas 1 gravatar image

Thomas 1
51 1

I disagree. You're not taking into account deprecation and removal of features.
May 23, 2010 at 11:35 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 17, 2010 at 02:16 AM

venkatreddy gravatar image

562 28 29 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 05, 2010 at 02:20 PM

Seen: 3238 times

Last Updated: Mar 05, 2010 at 02:20 PM