May 25, 2009 at 8:24 am
I have one SQL Server instance that was migrated from SQL Server 2000 to 2005. The new databases are with the compatibility level 90, but some of them are still 80.
I don't want to change the compatibility level only to "get the latest version", because I know there are some risks, so I whant to know are the advantages of changing the compatibility level from 80 to 90? I've made some tests and the new system views an system tables exists in the database with compatibility 80, I can create schemas etc.
So, in fact, there is really something that I can take advantage of, by changing the compatibility level?
I don't have problems with *= JOINS nor with ORDER BY clauses.
Thanks in Advance
________________
DBA Cabuloso
Lucas Benevides
May 25, 2009 at 8:37 am
for me, the biggest advantage for switching to compatibility 90 is the row_number() function; I've solved a couple of important SQL's with that function easier than if I had to use a temp table in 80 syntax; that function has been a huge help.
The other day I had to do a row_number over Partition by equivalent for a 2000 client, and I had forgotten just how much extra work was involved.
concatenating columns into a single field with the "FOR XML PATH(''))", is another feature that I use a lot, but that might work in 2000, I"m not sure.
Lowell
May 25, 2009 at 9:21 am
If no 90 features are needed I would keep it at 80 level -specially if talking about an already stable production environment.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 25, 2009 at 9:24 am
AFAIK, the "compatibility level" is a syntax-only setting. That is, it only affects the T-SQL syntax recognized and implemented.
There are a very few other areas and tools on SQL Server that are affected by this. For instance, I believe that some of the SSMS built-in management reports will not work on level 80 (though this may have been fixed in SP1).
But primarily it's the 2005 T-SQL syntax and features that you are giving up These are the big ones:
* CTE's, including recursive CTE's
* Windowed Aggregates (Row_Number(), Rank(), etc.)
* XML Data type, XML methods, FOR XML improvements
* TRY-CATCH error-handling
* varchar(MAX), nvarchar(MAX) and varbinary(MAX)
Others inlcude:
* DDL Triggers
* Event & query Notifications
* Service Broker
Additionally, I believe that you cannot take advantage of SQLCLR either (not sure about this one though).
(EDIT: Note, the above is not correct. Most of these features are still available under compatibility level 80).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 25, 2009 at 10:17 am
... and all those features are a major reason for compatibility level 9.0.
I have to support SSE2k databases in combination with SSE2k5 databases. The new features are really great and enable better and faster development.
Greets
Flo
May 25, 2009 at 10:42 am
Friends,
I have just tested in the following way:
a) Created a new database in SQL Server 2005 and in the "Compatibility level" I chose 80.
b) Created a new table in this database
c) Created a stored procedure using BEGIN TRY ... BEGIN CATCH
d) Fired a query using ROW_NUMBER().
Every step worked perfectly. That's why I am curious about this compatibility level. I just think it has no practical effects in the database except from those listed in http://msdn.microsoft.com/en-us/library/bb510680.aspx, which represents none advantage in my point of view. Unless my test is compromised by the fact that I am creating the database already in SQL Server 2005.
I am using SQL Server 2005, SP3 version 9.00.4035.
________________
DBA Cabuloso
Lucas Benevides
May 25, 2009 at 12:25 pm
Well, you do seem to be correct about this and I will note it on my earlier post.
Nonetheless, the article that you point to also clearly indicates that a lowered Compatibility Level is intended only as an interim migration aid, and NOT as a permanent condition.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2009 at 3:04 am
Hi !
A question about this....
I created an empty database in Sql 2005, restored a SQL 2000 database into that one instead of detach, attach db.
Now that database has comp. level 80.
Can I just change the comp. level to get the benefits of sql 2005 or do I have to redo it by using attach_db ??
With Regards
Jonas
May 26, 2009 at 6:37 am
You can just change it. You should make sure that no one else is in it (put it in single user mode first) just to insure that nothing freaky happens.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply