SQL Server Performance Forum – Threads Archive
Compatibility level in SQL 2k5
For SQL 2k5 compatibility level, I want to make sure I understand it correctly. #1. If I set the compatibility level to 8.0, it means SQL is at the SQL 2K level but can’t use any features in SQL 2k5. #2. If I set the compatibility level to 9.0, it means SQL is at SQL 2k5 level. I will be able to use both SQL 2k and SQL 2k5 features except that I may need to modify some code existed in SQL 2K that may have conflict with SQL 2k5. For example, pivot is now an operator in SQL 2K5. I used Pivot as an alais field name in SQL 2K and caused problem in the code. wingman1. Yes
2. Yes, but limited to few issues on database compatibility. SQL server 2005 books online has got more information on compatibility featuers. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks for the quick reply. The reason I am asking the question especially on #1 is because I am quite confused by it. Let me give you an example. In SQL 2K5, there is a new feature ‘Create Assembly statement’. In a SQL 2K5 database that is set to compatibility level to 8.0, the create Assembly statement is still working. Based on what I thought and your answer, this statement shouldn’t be working under that compatibility level. Based on this, it seems compatibility level 8.0 allows the database to use both old SQL 2K features and the new SQL 2K5 features. Compatibility level 9.0 enforces all the new changes which requires code modification. Please advice. wingman
quote:Originally posted by satya
1. Yes
2. Yes, but limited to few issues on database compatibility. SQL server 2005 books online has got more information on compatibility featuers. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
2. Yes, but limited to few issues on database compatibility. SQL server 2005 books online has got more information on compatibility featuers. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
If your master database compatibilty is 8.0 you can’t create Assembly…
In your case may be your master is 9.0 cmpt…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Interesting… You are correct that the master db is set to 9.0 cmpt. If I keep this as it is, which is master as 9.0 and user db’s as 8.0, will this be the workaround to allow us to use both 8.0 and 9.0 features? what are the consequence? The BOL doesn’t give me this type of setup unless I miss it.
quote:Originally posted by MohammedU
If your master database compatibilty is 8.0 you can’t create Assembly…
In your case may be your master is 9.0 cmpt…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
In your case may be your master is 9.0 cmpt…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
At the server level you can but for the user databases it will not be. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Ok, I think I understand a little better now. Let me know if the following is correct. say the master db is at 9.0 level and a user db is at 8.0 level. 1. the Create Assembly statement is still working in the user db because the master db is at 9.0 and the Create Assembly statement is a server level command not user db level. 2. If I have some new SQL features such as Pivot operator that are only available in SQL 2k5 and try to implement it in a SQL statement within a user db level, based on the above compat. level setup, those SQL statements won’t work. Wingman
quote:Originally posted by satya
At the server level you can but for the user databases it will not be.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
1. I think so…
2. You get the error like the one following …
"Incorrect syntax near ‘UNPIVOT’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel." MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
If possible you can test it on the dev. environment to ensure they have been addressed as per the needs. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>