Home

	Expand Search

What's New in SYBASE SQL Server Release 10.0?

Chapter 1: New Features in SQL Server Release 10.0

ANSI Compatibility

ANSI Compatibility

SQL Server Release 10.0 meets SQL89 /FIPS 127-1 and SQL92/FIPS 127-2 standards. In addition to major features such as declarative integrity constraints and cursors, which are described elsewhere in this chapter, the following changes are included in Release 10.0. Since some of ANSI behavior is not compatible with existing SQL Server applications, Transact-SQL provides set options that allow you to toggle these behaviors.

set Commands Required for ANSI Compliance

Compliant behavior is enabled by default for all Embedded SQLä precompiler applications. Other applications needing to match ANSI behavior can use option settings in Table 1-1: set Options for ANSI Compliance for entry level SQL92 compliance.

Table 1-1: : set Options for ANSI Compliance

Option	Setting
ansi_permissions	on
ansinull	on
arithabort	off
arithabort numeric_truncation	on
arithignore	off
chained	on
close on endtran	on
fipsflagger	on
quoted_identifier	on
string_rtruncation	on
transaction isolation level	3

The following sections describe the differences between standard behavior and the default Transact-SQL behavior. For more information on setting these options, see "set" in Chapter 1 of the SQL Server Reference Manual , Volume 1.

FIPS Flagger

For customers writing applications that must conform to the ANSI standard, SQL Server provides a set fipsflagger option to flag incompatible syntax. When this option is turned on, all commands containing Transact-SQL extensions that are not allowed in entry level SQL92 generate an informational message. See "set" in Volume 1, Chapter 1 of the SQL Server Reference Manual for more information.

SQLSTATE Messages and Codes

By default, SQL Server returns SQLSTATE values to embedded SQL applications, as required by entry level SQL92. SQLSTATE values are included in a new column in sysmessages , when SQLSTATE codes are defined in the standard. Some of the set commands listed in Table 1-1: set Options for ANSI Compliance cause the associated message text to be delivered to client applications such as isql .

The escape Clause in the like Predicate

SQL Server now supports the ANSI escape clause, which allows you to specify an escape character in the like predicate to search for wildcard characters. This is in addition to Transact-SQL's use of square brackets for the same purpose. See "Wildcard Characters" in Volume 1, Chapter 3 of the SQL Server Reference Manual .

ANSI-Style Comments

In Transact-SQL, comments are delimited by /* */ pairs, and can be nested. Transact-SQL now also supports ANSI-style comments, which consist of any string beginning with two unspaced minus signs, a comment, and a terminating newline:

 select "hello" -- this is a comment 

This syntax conflicts with the subtraction of a negative number. See Chapter 2, "Changes That May Affect Existing Applications" , for information on how this can affect existing applications. Transact-SQL's /* */ comments are still fully supported, and the "- -" within Transact-SQL comments is not recognized.

Changes to set Options arithabort and arithignore

Changes were made to the set options arithabort and arithignore to allow compliance with the SQL92 standard:

• arithabort arith_overflow determines how SQL Server handles arithmetic overflows and divide-by-zero errors during implicit and explicit conversions. Set this option on to roll back the entire transaction or batch in which the error occurs. Set this option off to abort the statement that contains the error and continue processing other statements in the transaction or batch.

• arithabort numeric_truncation determines how SQL Server handles loss of scale during implicit conversions to a numeric or decimal type. Set this option on to abort the statement that contains the error and continue processing other statements in the transaction or batch. Set it off to truncate the results as necessary and continue processing.

• arithignore arith_overflow determines whether SQL Server ignores arithmetic overflow and divide-by-zero errors. Set this option off to print a warning message when these errors occur. Set it on to ignore these errors and print no messages.

If you have used these options in your applications, examine them to ensure that they are still producing the desired behavior.

Synonymous Keywords

Several keywords have been added for ANSI compatibility that are synonymous with existing Transact-SQL keywords.

Table 1-2: : ANSI-Compatible Keyword Synonyms

Pre-Release 10.0 Transact-SQL	Additional Syntax
tran transaction	work
any	some
grant all	grant all privileges
revoke all	revoke all privileges
max ( expression )	max ([ all | distinct ]) expression
min ( expression )	min ([all | distinct]) expression
user_name built-in function	user keyword

The new work keyword is synonymous with tran and transaction only with the commit transaction and rollback transaction commands, not with the begin transaction command.

See "Datatypes" of this document for a list of synonymous datatypes.

Chained Transactions and Isolation Levels

SQL Server now provides ANSI-compliant "chained" transaction behavior as an option. In chained mode, all data retrieval and modification commands ( delete , insert , open , fetch , select , and update ) implicitly begin a transaction. Since such behavior is incompatible with many Transact-SQL applications, Transact-SQL style (or "unchained") transactions remain the default.

Warning! Before you change the default transaction mode or isolation level, be sure to read the sections in the Transact-SQL User's Guide and Volume 1 of the SQL Server Reference Manual that describe how these changes can affect existing applications and stored procedures.

Chained transaction mode can be initiated with a new option to the set command. Another set option controls transaction isolation levels. See "Transactions" in Volume 1 of the SQL Server Reference Manual for more information.

Delimited Identifiers

SQL Server now supports delimited identifiers for table, view, and column names. Delimited identifiers are object names enclosed within double quotation marks. Using them allows you to avoid certain restrictions on object names.

Delimited identifiers can begin with non-alphabetic characters, including characters that would not otherwise be allowed, or even be Transact-SQL reserved words. They cannot exceed 28 bytes.

Set the new quoted_identifier option on to allow delimited identifiers. While the option is on, do not use double quotes around character or date strings; use single quotes instead. Delimiting strings with double quotes causes SQL Server to treat them as identifiers.

Note: Delimited identifiers cannot be used with some system procedures, cannot be used with bcp , and may not be supported by all client software.

Treatment of Nulls

A new set option, ansinull , determines whether or not evaluation of NULL-valued operands in SQL equality (=) or inequality (!=) comparisons and in aggregate functions is ANSI-compliant. This option does not affect how SQL Server evaluates NULL values in other kinds of SQL statements, such as create table .

Right Truncation of Character Strings

A new set option, string_rtruncation , controls silent truncation of character strings for ANSI compatibility. Set this option on to prohibit silent truncation and enforce ANSI behavior.

Permissions Required for update and delete Statements

A new set option, ansi_permissions , determines what permissions are required for delete and update statements. When this option is on , SQL Server uses SQL92's more stringent permissions requirements for these statements. Because this behavior is incompatible with many existing applications, the default setting for this option is off .