SQL Data Types

The Microsoft Access database engine SQL data types consist of 13 primary data types defined by the Microsoft Access database engine and several valid synonyms recognized for these data types.

The following table lists the primary data types.

Data type

Storage size

Description

BINARY

1 byte per character

Any type of data may be stored in a field of this type. No translation of the data (for example, to text) is made. How the data is input in a binary field dictates how it will appear as output.

BIT

1 byte

Yes and No values and fields that contain only one of two values.

TINYINT

1 byte

An integer value between 0 and 255.

MONEY

8 bytes

A scaled integer between
– 922,337,203,685,477.5808 and 922,337,203,685,477.5807.

DATETIME
(See DOUBLE)

8 bytes

A date or time value between the years 100 and 9999.

UNIQUEIDENTIFIER

128 bits

A unique identification number used with remote procedure calls.

REAL

4 bytes

A single-precision floating-point value with a range of – 3.402823E38 to – 1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.

FLOAT

8 bytes

A double-precision floating-point value with a range of – 1.79769313486232E308 to – 4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.

SMALLINT

2 bytes

A short integer between – 32,768 and 32,767.

INTEGER

4 bytes

A long integer between – 2,147,483,648 and 2,147,483,647.

DECIMAL

17 bytes

An exact numeric data type that holds values from 1028 - 1 through - 1028 - 1. You can define both precision (1 - 28) and scale (0 - defined precision). The default precision and scale are 18 and 0, respectively.

TEXT

2 bytes per character (See Note)

Zero to a maximum of 2.14 gigabytes.

IMAGE

As required

Zero to a maximum of 2.14 gigabytes. Used for OLE objects.

CHARACTER

2 bytes per character (See Note)

Zero to 255 characters.


Note: Characters in fields defined as either TEXT (also known as MEMO) or CHAR (also known as TEXT(n) with a specific length) are stored in the Unicode representation format. Unicode characters uniformly require two bytes to store each character. For existing Microsoft Access databases that contain predominately character data, this could mean that the database file would nearly double in size when converted to the Microsoft Access format. Yet Unicode representation of many character sets, those formerly denoted as SBCS (Single-Byte Character Sets) can easily be compressed to a single byte. If you define a CHAR column with the COMPRESSION attribute, data will automatically be compressed as it is stored and uncompressed when it is retrieved from the column.



Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×