SQL Data Types
The Microsoft Jet database engine SQL data types consist of 13 primary data types defined by the Microsoft® Jet 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. (See Notes) |
| INTEGER | 4 bytes | A long integer between – 2,147,483,648 and 2,147,483,647. (See Notes) |
| 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 Notes) | 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 Notes) | Zero to 255 characters. |
Notes
- Both the seed and the increment can be modified using an ALTER TABLE statement . New rows inserted into the table will have values, based on the new seed and increment values, that are automatically generated for the column. If the new seed and increment can yield values that match values generated based on the preceding seed and increment, duplicates will be generated. If the column is a primary key , then inserting new rows may result in errors when duplicate values are generated.
- To find the last value that was used for an auto-increment column, you can use the following statement: SELECT @@IDENTITY. You cannot specify a table name. The value returned is from the last table, containing an auto-increment column, that was updated.
- 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 Jet databases that contain predominately character data, this could mean that the database file would nearly double in size when converted to the Microsoft Jet 4.0 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.