Decide what kind of data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) 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 (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.) or OLE object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.) fields.
- Do you want to sort or index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) 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 (OLE/DDE link: A connection between an OLE object and its OLE server, or between a Dynamic Data Exchange (DDE) source document and a destination document.) to or embedded (embed: To insert a copy of an OLE object from another application. The source of the object, called the OLE server, can be any application that supports object linking and embedding. Changes to an embedded object are not reflected in the original object.) in an OLE Object field in a Microsoft Access table. To display the OLE object, use a control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) in a form or report.
Choosing between a Text or Memo field
Microsoft Access provides two field data types (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or numeric characters, but a Number field can store only numerical data.) 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
Choosing between an incremented or random AutoNumber field
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.
