Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
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.
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.
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.
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.
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.
Email this link
Printer-Friendly Version
Share
Show All
Hide All
