sql server compatibility level

Question

hi there,

i've to downgrade a bunch of queries from sql server 2008 to 2000. some functions are not supported in 2k, like cte or row_number().

i've copied the database to an sql-2008 server, setting compatibility level to 80, hoping this db would behave like in a 2k-server. but i can easily run a query using cte or "not-supported" functions, what makes it difficult to determine which functions or queries have to be tuned!

what's my fault? thanks in advance!

Answer 1

Honestly I suggest just installing a local SQL 2000 and testing it there.

Answer 2

The compatability Level doesn't mean you're running the database in SQL server 2000 only.

Its a backward compability setting so if it sees T-SQL commands that are newer, it will happily run them regardless of your compatabilty level.

As Preet suggests to get a true test you really could do with installing a local copy of SQL Server 2000.