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 non-compliant with respect to a specific ISO/ANSI SQL standard, 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:

  • Spell out all of 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.
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0