Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

Compliance to ANSI standards

Compliance to ANSI standards

The progression of standards for relational database management systems is ongoing. These standards have been, and are being, adopted by ISO and several national standards bodies. SQL86 was the first of these standards. This was replaced by SQL89, which in turn was replaced by SQL92, which is the current standard. SQL92 defines three levels of conformance: entry, intermediate, and full. In the United States, the National Institute for Standards and Technology (NIST) has defined the transitional level, which falls between the entry and intermediate levels.

Certain behaviors defined by the standards are not compatible with existing SQL Server and Adaptive Server applications. Transact-SQL provides set options that allow you to toggle these behaviors.

By default, compliant behavior is enabled for all Embedded SQL(TM) precompiler applications. Other applications needing to match SQL standard behavior can use the options in Table 1-7 for entry-level SQL92 compliance. For more information on setting these options, see set in the Reference Manual.

The following sections describe the differences between standard behavior and default Transact-SQL behavior.

Federal Information Processing Standards (FIPS) flagger

For customers writing applications that must conform to the SQL92 standard, Adaptive Server provides a set fipsflagger option. When this option is turned on, all commands containing Transact-SQL extensions that are not allowed in entry-level SQL92 generate an informational message. This option does not disable the extensions. Processing completes when you issue the non-ANSI SQL command.

Chained transactions and isolation levels

Adaptive Server provides SQL standard-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.

You can initiate chained transaction mode using the set chained option. The set transaction isolation level option controls transaction isolation levels. See Chapter 18, "Transactions: Maintaining Data Consistency and Recovery," for more information.

Identifiers

To be compliant with entry-level SQL92, identifiers cannot:

• Begin with a pound sign (#)

• Have more than 18 characters

• Contain lowercase letters

Adaptive Server 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.

Use the set quoted_identifier option to recognize delimited identifiers. When this option is on, all characters enclosed in double quotes are treated as identifiers. Because this behavior is incompatible with many existing applications, the default setting for this option is off.

SQL standard-style comments

In Transact-SQL, comments are delimited by "/*" and "*/", and can be nested. Transact-SQL also supports SQL standard-style comments, which consist of any string beginning with two connected minus signs, a comment, and a terminating new line:

select "hello" -- this is a comment

The Transact-SQL "/*" and "*/" comment delimiters are fully supported, but "--" within Transact-SQL comments is not recognized.

Right truncation of character strings

The string_rtruncation set option controls silent truncation of character strings for SQL standard compatibility. Set this option to on to prohibit silent truncation and enforce SQL standard behavior.

Permissions required for update and delete statements

The ansi_permissions set option determines permissions required for delete and update statements. When this option is on, Adaptive Server uses the more stringent SQL92 permission requirements for these statements. Because this behavior is incompatible with many existing applications, the default setting for this option is off.

Arithmetic errors

The arithabort and arithignore set options allow compliance with the SQL92 standard as follows:

• arithabort arith_overflow specifies behavior following a divide-by-zero error or a loss of precision. The default setting, arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch, but Adaptive Server does not execute statements in the batch that follow the error-generating statement.

  If you set arithabort arith_overflow off, Adaptive Server aborts the statement that causes the error but continues to process other statements in the transaction or batch.

• arithabort numeric_truncation specifies behavior following a loss of scale by an exact numeric type. The default setting, on, aborts the statement that causes the error but continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing. For compliance with the SQL92 standard, enter set arithabort numeric_truncation on.

• arithignore arith_overflow determines whether Adaptive Server displays a message after a divide-by-zero error or a loss of precision. The default setting, off, displays a warning message after these errors. Setting arithignore arith_overflow on suppresses warning messages after these errors. For compliance to the SQL92 standard, enter set arithignore off.

Synonymous keywords

Several keywords added for SQL standard compatibility are synonymous with existing Transact-SQL keywords.

Treatment of nulls

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