tempdb compatibility level

[Michael Cheng]

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.

[John]

Thank you both for your responses

It is comforting to know that this setting is not causing any performance loss to the server

I have one more question though: Is it possible that by changing the compatibility level fromn 70 to 85 I can potentially break an application's functionality if it was using tempdb?

[Tibor Karaszi]

Yes, that is a risk, even if it is remote (IMO). An app can for instance do something like:

USE tempdb
CREATE TABLE function (collate int)
...

Those names were not keywords in 7.0, but are in 8.0. I find it rather unlikely that you have such code,
though. I suggest you check out the "SQL Server 2000 and SQL Server version 7.0" section in the updated Books
Online for a bit more info about compatibly issues.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp

Thank you both for your responses.
It is comforting to know that this setting is not causing any performance loss to the server.
I have one more question though: Is it possible that by changing the compatibility level fromn 70 to 85 I

can potentially break an application's functionality if it was using tempdb?

[Michael Cheng]

Hi, John

First of all, I think if you run 'exec sp_dbcmptlevel testdb, 85' you will
get the error message that Valid values of database compatibility level are
60, 65, 70, or 80.
You can only choose between 60, 65, 70, or 80, so I guess your 85 is a typo
:)

Secondly, Although the changing from 70 to 80 might potentially break the
application, it's rather LIMITED. The compatibility level is mainly focus
on some query behaviors and different reserved words.

You can find the difference from Books Online (search with sp_dbcmptlevel)
or MSDN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sp_da-di_5c8c.asp

Moreover, I would like to quote an example in MSDN:
This example creates a procedure named distributed, which is an SQL Server
reserved keyword, by setting the compatibility level setting for the pubs
database to 60.
CREATE PROCEDURE "distributed"
AS
PRINT 'This won''t happen'
EXEC sp_dbcmptlevel 'pubs', 60
CREATE PROCEDURE "distributed"
AS
PRINT 'You are in a procedure that could not be defined'
PRINT 'in a version of SQL Server 6.5 or later'
PRINT 'without the compatibility setting.'
EXEC "distributed"

Here is the result set:
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'distributed'.
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.
You are in a procedure that could not be defined
in a version of SQL Server 6.5 or greater
without the compatibility setting.


Hope this help and please feel free to post in the group if this solves
your problem or if you would like further help. 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.

[John]

Thank you both for your replies.

Yes- that was a typo...I meant 80. :

I think I am going to leave the setting as is @ 70 being that there doesn't appear to be anything wrong with the server and I won't get any performance gain or gain in functionality by changing it. The remote chance of breaking an app on this particular box makes me very weary about changing it. Especially since I don't have a way to test this change before actually making it(without going ahead and doing it that is!)