sql server (alternate): We are running SQL Server in 6.5 compatability mode. We have some queries
that are supported in 2000, that is the reason for the 6.5 version. What,
if any, would be gained in performance by switching to 2000.

[quoted text, click to view]

As far as I'm aware, the compatibility level affects functionality, not
performance as such, although since I haven't used it much myself, I'm not
100% sure about that.

In any event, MS recommends using a compatibility level only as a temporary
step during an upgrade, and moving to 2000 level would give you extra
functionality (eg. functions, indexed views) which isn't available in 6.5
mode. So it would probably be a good idea to review and update your code
anyway.

If you're having difficulties doing that, then you can post an example of
the problem code (including DDL and sample data, if appropriate), and the
errors you get in 2000 mode, and someone may be able to suggest an
alternative way to write the queries.

Simon

You would get all the gains of SQL-Server 7.0 and 2000.

Some relevant gains of SQL-Server 7.0:
- Use of multiple indexes per table
- Hash and merge join strategy
- better handling of large memory
- better storage engine

Some relevant gains of SQL-Server 2000:
- Indexed views possible
- even better storage engine

Gert-Jan

[quoted text, click to view]