SqlServer 2000 compatibility

Question

The developer environment db server is SqlServer 2005 (developer edition)

Is there any way to make sure my SQL Queries will run in SqlServer 2000?

This database is set to Compatibility level "SQL Server 2000 (80)" but some queries that run without problems in the development system can not run in the Test Server (SqlServer).

(The problems seems to be in subqueries)

Answer 1

Compatibility levels are designed to work the opposite way - to allow an older version of T-SQL code to work without modifications on a newer version of SQL Server. The changes typically involve T-SQL syntax and reserved words, and it's possible to use SQL Server 2005 features such as INCLUDED columns in indexes on a database in Compatibility Level 80. However, you can't use 2005 T-SQL features such as CROSS APPLY.

Your best option is to develop/test all your code against a SQL Server 2000 instance. Note that you can use 2005's Management Studio to connect to the SQL Server 2000 instance, so you don't have to go backwards with regards to tools.

Answer 2

Problem solved:

In correlated subqueries you have to (in SQL2000) explicitly define the external field.

SQL2005:

SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE COLLATERAL_LOAN=LOAN_NUMBER)

SQL2000:

SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE COLLATERAL_LOAN=Loans.LOAN_NUMBER)

Answer 3

You should always explicitly define all fields, otherwise you will not get an error when you make a mistake and write

SELECT * FROM Loans WHERE EXISTS (SELECT * FROM Collaterals WHERE LOAN_NUMBER=Loans.LOAN_NUMBER)

If Collaterals-table doesn't have column LOAN_NUMBER, the Loans-table is used instead.