SQL Server 2000 vs SQL Server 2008 Query Performance

Question

I'm working with a client who had a SQL Server 2008 converted from a SQL Server 2000 DB and one of the queries has quite dramatically increased in time since it was on SQL Server 2000.

However, if I change the compatibility level to 2008 in the DB, the query goes like a rocket (40-50 times faster).

The query does use a number of UDFs.

My questions: - are there issues with running SQL2000 compatibility in SQL Server 2008 - has SQL Server 2008 improved the performance when using UDFs?

Answer 1

There are some other things you might want to do after upgrading. See the "After upgrading..." section here: http://msdn.microsoft.com/en-us/library/bb933942.aspx