MS .NET Data Types
The following table shows the Visual Basic .NET data types, their supporting common language runtime types, their nominal storage allocation, and their value ranges.
| Visual Basic type | Common language runtime type structure | Nominal storage allocation | Value Range |
|---|---|---|---|
| Boolean | System.Boolean | 2 bytes | True or False. |
| Byte | System.Byte | 1 byte | 0 through 255 (unsigned). |
| Char | System.Char | 2 bytes | 0 through 65535 (unsigned). |
| Date | System.DateTime | 8 bytes | 0:00:00 on January 1, 0001 through 11:59:59 PM on December 31, 9999. |
| Decimal | System.Decimal | 16 bytes | 0 through +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; 0 through +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest nonzero number is +/-0.0000000000000000000000000001 (+/-1E-28). |
| Double (double-precision floating-point) |
System.Double | 8 bytes | -1.79769313486231570E+308 through -4.94065645841246544E-324 for negative values; 4.94065645841246544E-324 through 1.79769313486231570E+308 for positive values. |
| Integer | System.Int32 | 4 bytes | -2,147,483,648 through 2,147,483,647. |
| Long (long integer) |
System.Int64 | 8 bytes | -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. |
| Object | System.Object (class) | 4 bytes | Any type can be stored in a variable of type Object. |
| Short | System.Int16 | 2 bytes | -32,768 through 32,767. |
| Single (single-precision floating-point) |
System.Single | 4 bytes | -3.4028235E+38 through -1.401298E-45 for negative values; 1.401298E-45 through 3.4028235E+38 for positive values. |
| String (variable-length) |
System.String (class) | Depends on implementing platform | 0 to approximately 2 billion Unicode characters. |
| User-Defined Type (structure) |
(inherits from System.ValueType) | Depends on implementing platform | Each member of the structure has a range determined by its data type and independent of the ranges of the other members. |
Note: For strings containing text, use the StrConv function to convert from one text format to another.
VB 6.0 Data Types
The following table shows the supported data types, including storage sizes and ranges.
| Byte | 1 byte | 0 to 255 |
| Boolean | 2 bytes | True or False |
| Integer | 2 bytes | -32,768 to 32,767 |
| Long (long integer) |
4 bytes | -2,147,483,648 to 2,147,483,647 |
| Single (single-precision floating-point) |
4 bytes | -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values |
| Double (double-precision floating-point) |
8 bytes | -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values |
| Currency (scaled integer) |
8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 14 bytes | +/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001 |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| Object | 4 bytes | Any Object reference |
| String (variable-length) |
10 bytes + string length | 0 to approximately 2 billion |
| String (fixed-length) |
Length of string | 1 to approximately 65,400 |
| Variant (with numbers) |
16 bytes | Any numeric value up to the range of a Double |
| Variant (with characters) |
22 bytes + string length | Same range as for variable-length String |
| User-defined (using Type) |
Number required by elements | The range of each element is the same as the range of its data type. |
Note: Arrays of any data type require 20 bytes of memory plus 4 bytes for each array dimension plus the number of bytes occupied by the data itself. The memory occupied by the data can be calculated by multiplying the number of data elements by the size of each element. For example, the data in a single-dimension array consisting of 4 Integer data elements of 2 bytes each occupies 8 bytes. The 8 bytes required for the data plus the 24 bytes of overhead brings the total memory requirement for the array to 32 bytes.
A Variant containing an array requires 12 bytes more than the array alone.
Note: Use the StrConv function to convert one type of string data to another.
Microsoft SQL Server 2000 Data Types
SQL Server 2000 ships with 27 built-in (system) data types. They are:
| Data Types | Value Range |
|---|---|
| bigint | Integer data from -2^63 through 2^63-1 |
| int | Integer data from -2^31 through 2^31 - 1 |
| smallint | Integer data from -2^15 through 2^15 - 1 |
| tinyint | Integer data from 0 through 255 |
| bit | Integer data with either a 1 or 0 value |
| decimal | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
| numeric | Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
| money | Monetary data values from -2^63 through 2^63 - 1 |
| smallmoney | Monetary data values from -214,748.3648 through +214,748.3647 |
| float | Floating precision number data from -1.79E + 308 through 1.79E + 308 |
| real | Floating precision number data from -3.40E + 38 through 3.40E + 38 |
| datetime |
Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds |
| smalldatetime |
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute |
| char | Fixed-length character data with a maximum length of 8,000 characters |
| varchar | Variable-length data with a maximum of 8,000 characters |
| text | Variable-length data with a maximum length of 2^31 - 1 characters |
| nchar | Fixed-length Unicode data with a maximum length of 4,000 characters |
| nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters |
| ntext | Variable-length Unicode data with a maximum length of 2^30 - 1 characters |
| binary | Fixed-length binary data with a maximum length of 8,000 bytes |
| varbinary | Variable-length binary data with a maximum length of 8,000 bytes |
| image | Variable-length binary data with a maximum length of 2^31 - 1 bytes |
| cursor | A reference to a cursor |
| sql_variant |
A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant |
| table | A special data type used to store a result set for later processing |
| timestamp |
A database-wide unique number that gets updated every time a row gets updated |
| uniqueidentifier | A globally unique identifier |
Some of these data types (bigint, sql_variant, and table) are only available in SQL Server 2000, while some were supported under the previous SQL Server versions.
Note: SQL Server 2000 supports user-defined data types too. User-defined data types 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. Using user-defined data type can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values. To create a user-defined data type, you can use the sp_addtype system stored procedure or you could add one using the Enterprise Manager.
Microsoft Access 2000 Data Types
The following table summarizes all the field data types available in Microsoft Access, their uses, and their storage sizes.
| Data Type | Use For | Size |
|---|---|---|
| Text | Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. | Up to 255 characters. Microsoft Access only stores the characters entered in a field; it does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered, set the FieldSize property. |
| Memo | Lengthy text and numbers, such as notes or descriptions. | Up to 64,000 characters. |
| Number | Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the FieldSize property to define the specific Number type. | 1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only. |
| Date/Time | Dates and times. | 8 bytes |
| Currency | Currency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right. | 8 bytes |
| Auto-number | Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added. | 4 bytes. 16 bytes for Replication ID (GUID) only. |
| Yes/No | Fields that will contain only one of two values, such as Yes/No, True/False, On/Off. | 1 bit |
| OLE Objects | Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol that can be linked to or embedded in a Microsoft Access table. You must use a bound object frame in a form or report to display the OLE object. | Up to 1 gigabyte (limited by disk space). |
| Hyperlink | Field that will store hyperlinks. A hyperlink can be a UNC path or a URL. | Up to 64,000 characters. |
| Lookup Wizard | Creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts a wizard to define this for you. | The same size as the primary key field that is also the Lookup field; typically 4 bytes. |
About Field Properties: Each field has a set of properties that you use to customize how a field's data is stored, handled, or displayed. For example, you can control the maximum number of characters that can be entered into a Text field by setting its FieldSize property. You set a field's properties by displaying a table in Design view, selecting the field in the upper portion of the window, and then selecting the desired property in the lower portion of the window.
The properties that are available for each field are determined by the data type that you select for the field. The following table lists the available field properties in a Microsoft Access database. If a certain property does not appear in the property sheet for a field, it is not available for that field's data type.
| Field Property | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| Field Size | You can use the FieldSize property to set the maximum size for data stored in a field set to the Text, Number, or AutoNumber data type. | ||||||||
| Format | You can use the Format property to customize the way numbers, dates, times, and text are displayed and printed. The Format property only controls how the information in the field is displayed. It does not store the information as formatted. | ||||||||
| Input Mask | You can use the InputMask property to make data entry easier and to control the values users can enter in a text box control. | ||||||||
| Caption | You can use the Caption property to provide helpful information to the user
through captions on objects in various views:
|
||||||||
| Default Value | Specifies a String value that is automatically entered in a field when a new record is created. For example, in an Addresses table you can set the default value for the City field to New York. When users add a record to the table, they can either accept this value or enter the name of a different city. | ||||||||
| Validation Rule | You can use the ValidationRule property to specify requirements for data entered into a record, field, or control. When data is entered that violates the ValidationRule setting, you can use the ValidationText property to specify the message to be displayed to the user. | ||||||||
| Validation Text | Specifies the message to be displayed to the user when a validation rule is violated. | ||||||||
| Required | You can use the Required property to specify whether a value is required in a field. If this property is set to Yes, when you enter data in a record, you must enter a value in the field or in any control bound to the field, and the value cannot be Null. For example, you might want to be sure that a LastName control has a value for each record. When you want to permit Null values in a field, you must not only set the Required property to No but if there is a ValidationRule property setting, it must also explicitly state "validationrule Or Is Null". | ||||||||
| Allow Zero Length | You can use the AllowZeroLength property to specify whether a zero-length string (" ") is a valid entry in a table field. | ||||||||
| Indexed | You can use the Indexed property to set a single-field index. An index speeds up queries on the indexed fields as well as sorting and grouping operations. For example, if you search for specific employee names in a LastName field, you can create an index for this field to speed up the search for a specific name. | ||||||||
| Unicode Compression | Microsoft Access 2000 and later use the Unicode character-encoding scheme to represent
the data in a Text, Memo, or Hyperlink field. Unicode represents each character as two bytes,
so the data in a Text, Memo, or Hyperlink field requires more storage space than it did in
Access 97 or earlier, where each character is represented as one byte. To offset this effect of Unicode character representation and to ensure optimal performance, the default value of the Unicode Compression property for a Text, Memo, or Hyperlink field is Yes. When a fields Unicode Compression property is set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved. Because the first byte of a Latin character - a character of a Western European language such as English, Spanish, or German - is 0, Unicode character representation does not affect how much storage space is required for compressed data that consists entirely of Latin characters. In a single field, you can store any combination of characters that Unicode supports. However, if the first byte of a particular character is not 0, that character is not compressed. Data in a Memo field is not compressed unless it requires 4,096 bytes or less of storage space after compression. As a result, the contents of a Memo field might be compressed in one record, but might not be compressed in another record. |
||||||||
| Decimal Places | You can use the DecimalPlaces property to specify the number of decimal places Microsoft Access uses to display numbers. Read/write Byte. | ||||||||
| New Values | You can use the NewValues property to specify how AutoNumber fields increment when new records are added to a table. The NewValues property applies only to AutoNumber fields. |