Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Other SQL Dialects » Configuring databases for Transact-SQL compatibility

Creating a Transact-SQL-compatible database Next Page

Setting options for Transact-SQL compatibility


You set SQL Anywhere database options using the SET OPTION statement. Several database option settings are relevant to Transact-SQL behavior.

Set the allow_nulls_by_default option

By default, Adaptive Server Enterprise disallows NULLs on new columns unless you explicitly define the column to allow NULLs. SQL Anywhere permits NULL in new columns by default, which is compatible with the SQL/2003 ISO standard.

To make Adaptive Server Enterprise behave in a SQL/2003-compatible manner, use the sp_dboption system procedure to set the allow_nulls_by_default option to true.

To make SQL Anywhere behave in a Transact-SQL-compatible manner, set the allow_nulls_by_default option to Off. You can do this using the SET OPTION statement as follows:

SET OPTION PUBLIC.allow_nulls_by_default = 'Off';
Set the quoted_identifier option

By default, Adaptive Server Enterprise treats identifiers and strings differently than SQL Anywhere, which matches the SQL/2003 ISO standard.

The quoted_identifier option is available in both Adaptive Server Enterprise and SQL Anywhere. Ensure the option is set to the same value in both databases, for identifiers and strings to be treated in a compatible manner.

For SQL/2003 behavior, set the quoted_identifier option to On in both Adaptive Server Enterprise and SQL Anywhere.

For Transact-SQL behavior, set the quoted_identifier option to Off in both Adaptive Server Enterprise and SQL Anywhere. If you choose this, you can no longer use identifiers that are the same as keywords, enclosed in double quotes. As an alternative to setting quoted_identifier to Off, ensure that all strings used in SQL statements in your application are enclosed in single quotes, not double quotes.

For more information on the quoted_identifier option, see quoted_identifier option [compatibility].

Set the automatic_ timestamp option to On

Transact-SQL defines a timestamp column with special properties. With the automatic_timestamp option set to On, the SQL Anywhere treatment of timestamp columns is similar to Adaptive Server Enterprise behavior.

With the automatic_timestamp option set to On in SQL Anywhere (the default setting is Off), any new columns with the TIMESTAMP data type that do not have an explicit default value defined receive a default value of timestamp.

For information on timestamp columns, see The special Transact-SQL timestamp column and data type.

Set the string_rtruncation option

Both Adaptive Server Enterprise and SQL Anywhere support the string_rtruncation option, which affects error message reporting when an INSERT or UPDATE string is truncated. Ensure that each database has the option set to the same value.

For more information on the string_rtruncation option, see string_rtruncation option [compatibility].

For more information on database options for Transact-SQL compatibility, see Compatibility options.