SQL dialect
<< Structured Query Language | SQL Language Reference | Query >>
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).
See also:
Structured Query Language
SET SQL DIALECT
Firebird Database Housekeeping Utility: Setting the database dialect
Firebird Interactive SQL Utility: SQL dialects
back to top of page
<< Structured Query Language | SQL Language Reference | Query >>
