Decide what kind of data type to use for a field based on these considerations:
- What kind of values do you want to allow in the field? For example, you can't store text in a field with a Number data type.
- How much storage space do you want to use for values in the field?
- What types of operations do you want to perform on the values in the field? For example, Microsoft Access can sum values in Number or Currency fields, but not values in Text or OLE object fields.
- Do you want to sort or index a field? OLE Object fields can't be sorted or indexed.
- Do you want to use a field to group records in queries or reports? OLE Object fields can't be used to group records.
- How do you want to sort values in a field? In a Text field, numbers sort as strings of characters (1, 10, 100, 2, 20, 200, and so on), not as numeric values. Use a Number or Currency field to sort numbers as numeric values. Also, many date formats will not sort properly if entered in a Text field. Use a Date/Time field to ensure proper sorting for dates.
-
Will you need to store Microsoft Word or Microsoft Excel documents, pictures, sound, and other types of binary data created in other programs? OLE objects can be linked to or embedded in an OLE Object field in a Microsoft Access table. To display the OLE object, use a control in a form or report.
Choosing between a Text or Memo field
Microsoft Access provides two field data types to store data with text or combinations of text and numbers: Text and Memo.
Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. The FieldSize property controls the maximum number of characters that can be entered in a Text field.
Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.
Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.
You can sort or group on a Text field or a Memo field, but Access only uses the first 255 characters when you sort or group on a Memo field.
Choosing between a Number or Currency field
Microsoft Access provides two field data types to store data containing numeric values: Number and Currency.
Use a Number field to store numeric data to be used for mathematical calculations, except calculations that involve money or that require a high degree of accuracy. The kind and size of numeric values that can be stored in a Number field is controlled by setting the FieldSize property. For example, the Byte field size will only store whole numbers (no decimal values) from 0 to 255 and occupies 1 byte of disk space.
Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. A Currency field occupies 8 bytes of disk space.
Number and Currency fields provide predefined display formats, or you can create a custom format.
Choosing between an incremented or random AutoNumber field
Microsoft Access provides the AutoNumber data type to create fields that automatically enter a unique number when a record is added. Once a number is generated for a record, it can't be deleted or changed. An AutoNumber field can generate three kinds of numbers: sequential numbers that increment by one, random numbers, and Replication ID (also referred to as GUIDs
About Access project (SQL Server) data types
Objects that contain data have an associated data type that defines the kind of data (character, integer, binary, and so on) the object can contain. The following objects have data types:
- Columns in tables and views.
- Parameters in stored procedures.
- Variables.
- Transact-SQL functions that return one or more data values of a specific data type.
- Stored procedures that have a return code, which always has an integer data type.
Assigning a data type to an object defines four attributes of the object:
- The kind of data contained by the object. For example, character, integer or binary.
- The length of the stored value, or its size. The length of an image, binary, and varbinary data type is defined in bytes. The length of any of the numeric data types is the number of bytes required to hold the number of digits allowed for that data type. The length of the character string and Unicode data types is defined in characters.
- The precision of the number (numeric data types only). The precision is the number of digits the number can contain. For example, a smallint object can hold a maximum of 5 digits; it has a precision of 5.
- The scale of the number (numeric data types only). The scale is the number of digits that can be stored to the right of the decimal point. For example, an int object cannot accept a decimal point and has a scale of 0. A money object can have a maximum of 4 digits to the right of the decimal point and has a scale of 4.
For example, if an object is defined as money, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.
All data stored in SQL Server must be compatible with one of these base data types. The cursor data type is the only base data type that cannot be assigned to a table column. It can be used only for variables and stored procedure parameters.
User-defined data types can also be created, for example:
-- Create a birthday datetype that allows nulls.
EXEC sp_addtype birthday, datetime, 'NULL'
-- Create a table using the new data type.
CREATE TABLE employee
emp_id char(5)
emp_first_name char(30)
emp_last_name char(40)
emp_birthday birthday
User-defined data types are always defined in terms of a base data type. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type.
Comparison or mapping of data types between an Access database and Access project
The following table compares data types between a Microsoft Access database and a Microsoft Access project.
| Microsoft Access data type | SQL Server data type |
|---|---|
| Yes/No | bit |
| Number (Byte) | tinyint |
| Number (Integer) | smallint |
| Number (Long Integer) | int |
| Number (Single) | real |
| (no equivalent) | bigint |
| Number (Double) | float |
| Currency | money |
| Decimal/numeric | decimal |
| Date/Time | datetime |
| AutoNumber (Increment) | int (with the Identity property defined) |
| Text (n) | varchar(n) |
| Memo | text |
| OLE Object | image |
| Replication ID (also called globally unique identifier (GUID)) | uniqueidentifier (SQL Server 7.0 or later) |
| Hyperlink | char, nchar, varchar, nvarchar (With the Hyperlink property set to Yes) |
| (no equivalent) | varbinary |
| (no equivalent) | smallint |
| (no equivalent) | timestamp |
| (no equivalent) | char
nchar |
| (no equivalent) | sql_variant |
| (no equivalent) | user-defined |
Note In an Access project or SQL Server database, the "n" prefix stands for "national" and means that the data type is unicode-enabled. In an Access database, all text columns are unicode-enabled by default.