Export (0) Print
Expand All

SQL Data Types

Office 2007

The Microsoft Access 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. The synonyms are identified in Microsoft Access Database Engine SQL Reserved Words.

Data typeStorage sizeDescription
BINARY1 byte per characterAny 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.
BIT1 byteYes and No values and fields that contain only one of two values.
TINYINT1 byteAn integer value between 0 and 255.
MONEY8 bytesA scaled integer between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807.
DATETIME (See DOUBLE)8 bytesA date or time value between the years 100 and 9999.
UNIQUEIDENTIFIER128 bitsA unique identification number used with remote procedure calls.
REAL4 bytesA 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.
FLOAT8 bytesA 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.
SMALLINT2 bytesA short integer between – 32,768 and 32,767. (See Notes)
INTEGER4 bytesA long integer between – 2,147,483,648 and 2,147,483,647. (See Notes)
DECIMAL17 bytesAn 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.
TEXT2 bytes per character (See Notes)Zero to a maximum of 2.14 gigabytes.
IMAGEAs requiredZero to a maximum of 2.14 gigabytes. Used for OLE objects.
CHARACTER2 bytes per character (See Notes)Zero to 255 characters.
Bb208866.vs_note(en-us,office.12).gif  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.
See Also

Equivalent ANSI SQL Data Types

Microsoft Access Database Engine SQL Reserved Words



Community Additions

ADD
Show:
© 2014 Microsoft