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.

Data type
Storage size
Value Range
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:
* Field captions specify the text for labels attached to controls created by dragging a field from the field list and serves as the column heading for the field in table or query Datasheet view.
* Form captions specify the text that appears in the title bar in Form view.
* Report captions specify the title of the report in print preview.
* Button and label captions specify the text that appears in the control.
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.


Twitter