RE: tempdb compatibility level

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Michael Cheng (v-mingqc_at_online.microsoft.com)
Date: 04/01/04 

Date: Thu, 01 Apr 2004 03:03:09 GMT

Hi John,

As you know, Compatibility Level is used to set certain database behaviors
to be compatible with the specified earlier of Microsoft SQL Server.

>From MSDN Library or Books Online, you can find the description
"applications gain almost all of the benefits of the new performance
enhancements of SQL Server 2000. Applications still benefit from features
such as the improved query processor" for different Compatibility Level.

Secondly, you can found the Compatibility level setting in Detail at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sp_da-di_5c8c.asp and you could also have a reference at SQL Server
Comparisons Between Columns and Constants with Different Data Types
(http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com
:80/support/kb/articles/Q271/5/66.ASP&NoWebContent=1), which shows SQL
Server 2000 behavior differs from previous versions of SQL Server when
queries that involve comparisons between columns and constants with
different data types are run.

In conclusion, you could set your tempdb database compatibility level,
which might be set to 70 mistakenly, to 80. Anyway, no matter which
compatibility level you use, performance will remain fine according to
using same query processor in SQL Server.

Hope this help and thank you for your patience and cooperation. If you have
any question or concern, don't hesitate to let me know. We are here to be
of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Relevant Pages

  • Re: Compatibility Levels
    ... > Our product uses a mix of SQL Server 6.5 and SQL Server 2000. ... the compatibility level of the database remains at 65. ... > And despite being on SQL Server 2000, the compatibility level of 65> prohibits us from executing the ALTER COLUMN statement. ...
    (microsoft.public.sqlserver.programming)
  • Compatibility Level
    ... Have you seen the topic "SQL Server 2000 and SQL Server ... The effects of the compatibility level settings are ... Even when the database ...
    (microsoft.public.sqlserver.programming)
  • Compatibility Levels
    ... Our product uses a mix of SQL Server 6.5 and SQL Server 2000. ... Some of these clients upgraded to SQL Server 2000. ... the compatibility level of the database remains at 65. ... We are now trying to update the database to include primary keys (don't get ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL query
    ... Do you use SQL Server 2000 or an earlier version? ... what is the setting of the compatibility level? ... The COLLATE option was introduced with SQL Server 2000, ... with a compatibility setting of 70 or lower, ...
    (microsoft.public.sqlserver.programming)
  • RE: Sharepoint issue
    ... do you mean Microsoft SQL Server ... 833183 You receive a "Cannot connect to the configuration database" error ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)