Equivalent ANSI SQL Data Types

Microsoft Jet SQL Reference

Equivalent ANSI SQL Data Types

The following table lists ANSI SQL data types, their equivalent Microsoft Jet database engine SQL data types, and their valid synonyms. It also lists the equivalent Microsoft® SQL Server™ data types.

ANSI SQL
data type
Microsoft Jet
SQL data type

Synonym
Microsoft SQL
Server data type
BIT, BIT VARYING BINARY (See Notes) VARBINARY,
BINARY VARYING
BIT VARYING
BINARY, VARBINARY
Not supported BIT (See Notes) BOOLEAN, LOGICAL, LOGICAL1, YESNO BIT
Not supported TINYINT INTEGER1, BYTE TINYINT
Not supported COUNTER (See Notes) AUTOINCREMENT (See Notes)
Not supported MONEY CURRENCY MONEY
DATE, TIME, TIMESTAMP DATETIME DATE, TIME  (See Notes) DATETIME
Not supported UNIQUEIDENTIFIER GUID UNIQUEIDENTIFIER
DECIMAL DECIMAL NUMERIC, DEC DECIMAL
REAL REAL SINGLE, FLOAT4, IEEESINGLE REAL
DOUBLE PRECISION, FLOAT FLOAT DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER (See Notes) FLOAT
SMALLINT SMALLINT SHORT, INTEGER2 SMALLINT
INTEGER INTEGER LONG, INT, INTEGER4 INTEGER
INTERVAL Not supported   Not supported
Not supported IMAGE LONGBINARY,  GENERAL, OLEOBJECT IMAGE
Not supported TEXT  (See Notes) LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT (See Notes) TEXT
CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING CHAR (See Notes) TEXT(n), ALPHANUMERIC,  CHARACTER, STRING, VARCHAR, CHARACTER VARYING, NCHAR, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING (See Notes) CHAR, VARCHAR, NCHAR, NVARCHAR


Notes

  • The ANSI SQL BIT data type does not correspond to the Microsoft Jet SQL BIT data type. It corresponds to the BINARY data type instead. There is no ANSI SQL equivalent for the Microsoft Jet SQL BIT data type.
  • TIMESTAMP is no longer supported as a synonym for DATETIME.
  • NUMERIC is no longer supported as a synonym for FLOAT or DOUBLE. NUMERIC is now used as a synonym for DECIMAL.
  • A LONGTEXT field is always stored in the Unicode representation format.
  • If the data type name TEXT is used without specifying the optional length, for example TEXT(25), a LONGTEXT field is created. This enables CREATE TABLE statements to be written that will yield data types consistent with Microsoft SQL Server.
  • A CHAR field is always stored in the Unicode representation format, which is the equivalent of the ANSI SQL NATIONAL CHAR data type.
  • If the data type name TEXT is used and the optional length is specified, for example TEXT(25), the data type of the field is equivalent to the CHAR data type. This preserves backwards compatibility for most Microsoft Jet applications, while enabling the TEXT data type (without a length specification) to be aligned with Microsoft SQL Server.
See Also

Microsoft Jet Database Engine SQL Data Types