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

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » SQL dialects and compatibility » Writing compatible SQL statements


General guidelines for writing portable SQL

When writing SQL for use on more than one database management system, make your SQL statements as explicit as possible. Even if more than one server supports a given SQL statement, it may be a mistake to assume that default behavior is the same on each system.

In SQL Anywhere, the database server and the SQL preprocessor (sqlpp) can identify SQL statements that are vendor extensions, are not compliant with specific ISO/ANSI SQL standards, or are not supported by UltraLite. This functionality is called the SQL Flagger. See Testing SQL compliance using the SQL Flagger.

General guidelines applicable to writing compatible SQL include:

  • Include all the available options, rather than using default behavior.

  • Use parentheses to make the order of execution within statements explicit, rather than assuming identical default order of precedence for operators.

  • Use the Transact-SQL convention of an @ sign preceding variable names for Adaptive Server Enterprise portability.

  • Declare variables and cursors in procedures, triggers, and batches immediately following a BEGIN statement. SQL Anywhere requires this, although Adaptive Server Enterprise allows declarations to be made anywhere in a procedure, trigger, or batch.

  • Avoid using reserved words from either Adaptive Server Enterprise or SQL Anywhere as identifiers in your databases.

  • Assume large namespaces. For example, ensure that each index should have a unique name.