Project using two different versions of SQL Server

Question

I am a junior developer and I am going to lay my feet in my first "real" project. However, we are trying to find a way to install everything, since the current developer lives outside the country.

I was told to install certain programs, disruptive clients, and SQL Server 2000.

It doesn't seem like SQL Server 2000 can be installed on Windows 7. Are there any problems with me in a higher version of SQL Server, such as 2005? Is there a problem with stored procedures that cannot be correctly transferred from a version of SQL Server to another?

Again, I'm pretty new to this; please let me know if this is just a bad idea, impossibility and any other recommendations you can provide.

Answer 1

Newer versions of MSSQL have many functions that were not there in 2000 (multi-line inserts, new hash algorithms and VARCHAR (MAX), to name a few). If you use SQL Server Management Studio, it will not check these differences for you, even if you are connected to a SQL Server 2000 database - it automatically uses 2008 rules to highlight syntax. Because of this, it is easy to accidentally write code that is not compatible with 2000.

As for starting 2000, if you have installed the installation disk for an older version of Windows, you can start the VM ( http://www.microsoft.com/windows/virtual-pc/ ) and install the database server there. If your company has a separate development environment, you can also create a copy of the production database for work.

Answer 2

New versions of SQL Server bring in new language and database functions, if you write something using a function available in SQL 2005, not 2000, i.e. PIVOT, and then when you try to promote it to live, then it just get syntax error.

There is no translation, if you go back 11 years, you will still speak English, you just get a strange look if you talked about "Tweeting".

You can set the database compatibility level to an earlier version for the specific database you are working on. This will stop you using more advanced features.

Answer 3

For the most part, you will not run into problems, as you simply run the standard stored procedures and SQL statements.

However, there are a few things that may not work properly if you are not in sync. SQL2005 was a relatively major update and introduced completely new functionality.

I don’t know what you have for available resources - dollars, etc., but if you have an MSDN subscription at a level that gives you access to operating systems , then I would strongly recommend setting up a virtual machine with an older one version of Windows using MSDN licenses to support work even in the playing field.