what if i use sql 2005 with compatibilty mode 80
-
Dear All,
-
Some of the features which are for compatibility mode 90 will not be supported for 80.Minaz
"More Green More Oxygen !! Plant a tree today"
-
hi,
If u restore a sql 2000 DB in Sql 2005 u need to change the compatability to 90 from 80.Else many of the T-Sql features available in 2k5 will not be supported
Cheers
Deepak
[font="Verdana"]- Deepak[/font]
-
Thankyou vey much but what are those feature that will not work if mode is 80. can any body list down.
Thnx
Rashid
-
the disk usage report and the new t-sql things like try/catch. it's all in BOL.
-
Compatibility level setting of 80 or earlier
Compatibility level setting of 90
For locking hints in the FROM clause, the WITH keyword is always optional.
With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).
The *= and =* operators for outer join are supported with a warning message.
These operators are not supported; the OUTER JOIN keyword should be used.
WHEN binding the column references in the ORDER BY list to the columns produced by the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored.
For instance, an ORDER BY expression that is made up of a single two-part column (<table_alias>.<column>) that is used as a reference to a column alias in a SELECT list is accepted, but the table alias is ignored. For example, in the query
SELECT DISTINCT c1 = c1*-1 FROM t_table x ORDER BY x.c1, theORDER BYoperation does not occur on the specified source column (x.c1); instead it occurs on thec1column that is defined in the query.Errors are raised on column ambiguities. Column prefixes, if any, specified in ORDER BY are not ignored when binding to a column produced by the SELECT list.
The UNION of a variable-length column and a fixed length column produces a fixed-length column.
The UNION of a variable-length column and a fixed length column produces a variable-length column.
SET XACT_ABORT OFF is allowed inside a trigger.
SET XACT_ABORT OFF is not allowed inside a trigger.
The FOR BROWSE clause is allowed (and ignored) in views.
The FOR BROWSE clause is not allowed in views.
Views with CHECK OPTION are supported incorrectly if the view or a view it references contains TOP.
Views with CHECK OPTION are not supported if the view or a view it references contains TOP.
If a passthrough query against a remote data source [OpenRowset or OpenQuery] produces columns with duplicate names, the duplicate column names are ignored unless the columns are explicitly named in the query.
If a passthrough query against a remote data source [OpenRowset or OpenQuery] produces a column with duplicate column names, an error is raised.
Character string constants and varbinary constants of size greater than 8000 are treated as text, ntext, or image.
Character string constants and varbinary constants of size greater than 8000 are treated as type varchar(max) (or nvarchar(max) and varbinary(max), respectively). This can change the data type of the table created using SELECT … INTO if the SELECT list contains such expressions.
Comparisons between numeric types (smallint, tinyint, int, bigint, numeric, decimal, smallmoney, money) are done by converting the comparand with lower precedence in the type hierarchy to the type whose precedence is higher.
The numeric-type values are compared without conversions. This provides improved performance. However, this can cause some changes in behavior, especially in cases in which the conversion caused overflow exceptions.
Built-in metadata functions that take string arguments truncate their input if the input is longer than 4000 characters.
Built-in metadata functions raise an error if the truncation would result in the loss of nonspace characters.
The set of disallowed characters in an unquoted identifier remains unchanged.
The Transact-SQL parser supports the Unicode 3.2 standard, which changes the character classification for some international characters that are now not allowed in nondelimited identifiers.
SET ANSI_WARNINGS ON does not override the setting of SET ARITHABORT OFF for the case of floating point domain errors [that is, negative arguments for the log() function]. If ANSI_WARNINGS is ON but ARITHABORT is OFF, floating point domain errors do not cause the query to be terminated.
SET ANSI_WARNINGS ON completely overrides ARITHABORT OFF setting. Floating point domain errors in this case will cause the query to be terminated.
Non-integer constants are allowed (and ignored) in the ORDER BY clause.
Non-integer constants are not allowed in the ORDER BY clause.
Empty SET statement (with no SET option assignments) is allowed.
Empty SET clause is not allowed.
The IDENTITY attribute is not derived correctly for columns produced by a derived table.
The IDENTITY attribute is derived correctly for columns produced by derived tables.
The nullability property of arithmetic operators over floating point data type is always nullable.
The nullability property of arithmetic operators over the floating point data type is changed to nonnullable for the case where the inputs are nonnullable and ANSI_WARNINGS is ON.
In the INSERT .. SELECT statement with UNION, the types produced by the individual result sets are all converted to the destination result type.
In the INSERT .. SELECT statement with UNION, the dominant type of the various branches is determined, and the results are converted to that type before being converted to the destination table type.
In the SELECT .. FOR XML statement, the hex(27) (the ' character) and hex(22) (the " character) are always entitized, even where not required.
FOR XML entitizes hex(27)and hex(22) only where required. They are not entitized in the following situations:
· In attribute content, hex(27) (the ' character) is not entitized if attribute values are delimited with ", and hex(22) (the " character) is not entitized if attribute values are delimited with '.
· In element content, hex(27) and hex(22) are never entitized.
In FOR XML, the timestamp value is mapped to an integer.
In FOR XML, the timestamp value is mapped to an binary value.
For more information, see FOR XML Support for the timestamp Data Type.
In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 8 positions.
For example, using 8 positions, FOR XML represents the Unicode code point
U+10000as:<a_x00010000_ c1="1" />In FOR XML and OPENXML, high-range Unicode characters (3 bytes) in names are represented using 6 positions.
For example, using 6 positions, FOR XML represents the Unicode code point
U+10000as:<a_x010000_ c1="1" />In FOR XML, derived table mappings in AUTO mode are treated transparently.
For example:
Copy Code
USE AdventureWorks
CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
SELECT * FROM (SELECT a.id AS a,
b.id AS b FROM Test a
JOIN Test b ON a.id=b.id)
Test FOR XML AUTO;
When the compatibility level for AdventureWorks is set to 80, the above example produces:
<a a="1"><b b="1"/></a><a a="2"><b b="2"/></a>In FOR XML, derived table mappings in AUTO mode are treated opaquely.
When the compatibility level for AdventureWorks is set to 90, the preceding example produces:
<Test a="1" b="1"/><Test a="2" b="2"/>For more information about changes to AUTO mode, see AUTO Mode Enhancements.
String to money conversions support using a backslash character (\) as a currency symbol only in the Japanese and Korean languages.
The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.
For databases on versions of SQL Server earlier than SQL Server 2005, this new behavior breaks indexes and computed columns that depend on an ISNUMERIC return value that contains \ and for which the language is neither Japanese nor Korean.
The result of an arithmetic operator is always nullable, even if the operands are nonnullable and ANSI_WARNINGS or ARITHABORT is set ON.
When ANSI_WARNINGS or ARITHABORT are set to ON, the result of a floating point arithmetic operator is nonnullable, if both operands are nonnullable.
This change in nullability could cause failure when bcp is used to bulk export data that uses the binary format from a SQL Server 2000 table with a computed column that uses a floating point arithmetic operator and bcp or BULK INSERT is then used bulk import that data into a SQL Server 2005 table with the same definition.
Note:
When both options are OFF, the Database Engine marks the result as nullable. This is the same as in SQL Server 2000.
Domain errors are not controlled by ANSI_WARNINGS. ARITHABORT settings are honored, if ANSI_WARNINGS are set to OFF and there is no change to ARITHABORT.
Domain errors are also controlled by ANSI_WARNINGS and are severity 16 errors. If either ANSI_WARNINGS or ARITHABORT are ON, an error is thrown instead of returning NULL value. User scripts that depend upon ARITHABORT being set to OFF might be broken by this change.
In an INSERT SELECT from a UNION of different data types, each UNION branch is directly cast to the type of the target column of the INSERT. Even if the union used by itself could fail because of incompatible type conversions, the INSERT SELECT makes the UNION succeed because the branch to the result type of the UNION is never converted.
In SQL Server 2005, the result type of UNION is derived independently of INSERT SELECT. Each branch of UNION is cast to the result type of UNION, and then cast to the target column type of INSERT. If there are incompatible types in the UNION, the first cast might cause an error. To run in compatibility level 90, you must fix all incompatible type unions used inside INSERT SELECT.
For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is converted to nvarchar(4000). In SQL Server 2000, if a larger value is passed, it is silently truncated.
For built-in functions that take nvarchar as a parameter, if the supplied value is varchar, the value is still converted to nvarchar(4000). However, if a larger value is passed, SQL Server 2005 generates an error.
To run in compatibility level 90, you must fix any custom code that relies on the truncation behavior.
A union of fixed-length (char, binary, or nchar) string with a variable-length (varchar, varbinary, nvarchar) string returns a fixed-length result.
The union of a variable-size string and a fixed-size string returns a variable-size string.
To run in compatibility level 90, you must fix all the places (indexes, queries, and computed columns) that depend on the type resulting from a union of a variable-size type and a fixed-size type.
Object names containing the character 0xFFFF are valid identifiers.
Object names containing the character 0xFFFF are invalid identifiers and cannot be accessed.
To run in compatibility level 90, you must rename objects that contain this character.
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.
For example, the following
SELECT ISNUMERIC('121212,12')query returns 0. This indicates that the string121212,12is not numeric.In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.
For example, the following
SELECT ISNUMERIC('121212,12')query returns 1. This indicate that the string121212,12is numeric. -
And one more, you cant use Performance Dash board utility when the db is in Compatibility 80. This feature is available from SP2 and its really a nice feature for administrators.
Regards..Vidhya Sagar
SQL-Articles -
I searched the BOL but couldn't find the list of new t-sqls which will not work with compatibility level of 80. Little help please.
-
hi,fyi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm check it pls
[font="Verdana"]- Deepak[/font]
-
Thanks Deepak. But it doesn't list any new Sql 2005 features.(Like CTE, CLR Integration and Try.. Catch)
-
You can refer the following link
http://qa.sqlservercentral.com/columnists/sjones/3036.asp
http://www.solutionwerx.com/Uploads/NMUGPresentation.ppt
This will give you an idea of what are all the changed done in TSQL
Regards..Vidhya Sagar
SQL-Articles -
Hi I have just found out when trying to install a CLR .NET assembly that enables use of Regular Expressions through User Defined Functions that our production server (2005) is set to compatibility level of 80 and will not allow me to run the following command:
CREATE
FUNCTION [dbo].[udf_SQLRegExMatch](@Pattern [nvarchar](500), @MatchString [nvarchar](2000))
RETURNS
[bit]
AS
EXTERNAL
NAME [asbl_SQLRegExpr].[UserDefinedFunctions].[RegExMatch]
it returns with
Msg 325, Level 15, State 1, Procedure udf_SQLRegExMatch, Line 5
Incorrect syntax near 'EXTERNAL'. 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.
Our development server is set to level 90. What I want to know is the best way to change this level as I understand from a colleague it will use an ALTER Database statement and if I tried to change the level setting from Management console or run EXEC sp_dbcmptlevel MyDatabase, 90
it will try and get an exclusive lock on the DB which will never happen so it will hang doing nothing. Therefore I understand from reading an article on the web that the best way is to kill all connections, set to single user mode, change compatibility level and then change back to multi user mode.
1. Is that correct? And if so is it the best way of doing it?
2. Is there any way I could script and schedule this for a time when the DB is not used much eg 3am. Or is this not wise and does it need to be done by hand in case something goes wrong? (I am not a DBA)
3. I reckon the reason the production DB is at 80 is because we originally transfered from a 2k version. However if the dev db (at 90) is running okay then there shouldn't be any problems transferring the production db from 80 to 90 should there? At the moment I am using lots of new 2005 TSQL features such as ROW_NUMBER(), OVER(), TRY CATCH etc and they all seem to be running fine on both servers with the level at 80 or 90.
Thanks for any help.
-
Just a hunch, but what if you created the function in MSDB system database?
Might be handy alternative, so that any database could then use the function.
Mark -
Yes the MSDB is set up correctly with a level of 90 and I managed to create the assembly and udfs which can be accessed from the db I am using them from.
Thanks for that!
Still do you think I should change the level to 90 on the other DB at some stage? If so whats the best way of going around it?
Thanks
-
I don't know if changing to 90 would make a difference for you or not. I would check that list in BOL that William tried to post in here and see if there is anything that you need (or conversely, anything that will break) in your applications. For instance, since ISNUMERIC on a number string with embedded comma returns a different value in 2000 vs 2005, I would check your apps to see if this causes an issue.
If I would decide to do it, I would not trust having done scripted and unattended. I would test it on a dev box first, hopefully something close to production server environment. I would setup an early morning time, do a backup (even if a normal backup was done, can't have too many backups when making major changes like this), do the Alter statement, then test the apps. Ideally, it sounds like that if everything worked ok, then it would only take a few minutes. But I would have that backup handy in case anything was suspicious, and just restore it if anything looked the slightest bit wrong. If something was wrong, then I go back to dev box and see why wasn't it wrong there, and how to correct, fix, or workaround it. Then try again another day.
On the last client site I was working, we ran a converted SQL 2000 database on SQL 2005 as compatibility mode 80. I did not see any reason to change it, as none of those feature differences listed affected us, and I still could use Broker services, try/catch, varchar(max) and other new features. (Although I did have problems trying to setup a replication publication, I wonder if compatibility mode affected that? Hmm) I was concerned about changing to 90, because one of the main (and biggest) tables had a bad design with 15 text columns. Most of the values in those columns were null or very small textlengths. But I was worried about switching to 90 would try to move that data back into the row (since rowsize would be less than 8060), and either have a problem or take an unexpected long time. If I had to change it, I would probably BCP the table out, truncate it, and reload after the upgrade; but I just did not want to deal with that unless I had a solid reason to.
Hope this helps
Mark
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply