SQL dialect

Structured Query Language is a language for relational databases, which serves to define, manipulate, find and fetch data in a database.

There are currently two SQL dialects used with Firebird and InterBase®:

• Dialect 1 = database performance is fully compatible to InterBase® 5.6 and earlier (e.g. numeric up to 15 digits).
• Dialect 3 = all new functions in InterBase® 6 and upwards with SQL 92 features are available (e.g. numeric up to 18 digits).

For those that work with the BDE, this can only work with dialect 1 up to and including Delphi 6 (i.e. dialect 3 from Delphi 7 onwards).

Differences between dialects 1 and 3 include:

• The numeric (15 or 18) size.
• Large exact numerics: DECIMAL and NUMERIC data types with precision greater than 9 are stored as INT64 instead of DOUBLE PRECISION.
• The double quote (“) has changed from a synonym for the single quote (') to the delimiter for an object name.
• DATE and TIME data types have altered:
  • Dialect 1 = Date includes the date and time
  • Dialect 3 = Date = date, time = time, timestamp = date and time.

For new projects it is recommended that dialect 3 be specified.

Occasionally the question arises “What about SQL Dialect 2?”. Dialect 2 is similar to dialect 1, generates however warnings for all objects that are incompatible to Dialect 3 (i.e. only suitable for the client end); therefore, in principle, not really of importance.

The SQL dialect to be used in a database is specified when creating the database (IBExpert menu: Database / Create Database). It can subsequently be altered using the IBExpert menu Services / Database Properties (although watch out for possible dialect incongruencies, for example, the different date and time types).