Access, Sql Server, Oracle Comparison

Data Types

Field Types

Access 2000 SQL Server Oracle 8 Summary

Text Up to 255 characters

Char Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

Char Fixed length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.

VarChar Variable-length non-Unicode data with a maximum of 8,000 characters.

VarChar2 Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for a VARCHAR2

nChar Fixed-length Unicode data with a maximum length of 4,000 characters.

nChar Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.

nVarChar Variable-length Unicode data with a maximum length of 4,000 characters.

SysName sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.

nVarChar2 Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.

Memo Up to 64000 characters

Text Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.

nText Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.

Number 1, 2, 4, or 8 bype types are possible

BigInt Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). (8btye)

Int Long Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). (4 byte)

SmallInt Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767). (2 byte)

TinyInt Integer data from 0 through 255. (1 byte)

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.

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.

Number(p,s) Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

Date/Time DateTime 8 bytes

Date Valid date range from January 1, 4712 BC to December 31, 4712 AD.

SmallDateTime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

Currency 8 bytes 15 digits.4 digits

Money Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

SmallMoney Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

AutoNumber 4 bytes

Yes/No Bit 1 bit

OLE Object Up to 1 gig

Image Long Raw Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.

Raw Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

Clob A character large object containing single-byte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes.

nClob A character large object containing fixed-width multibyte characters. Variable-width character sets are not supported. Maximum size is 4 gigabytes. Stores national character set data.

Blob A binary large object. Maximum size is 4 gigabytes.

bFile Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

Hyperlink Text or combinations of text and numbers stored as text and used as a hyperlink address. A hyperlink address can have up to three parts:

text to display -- the text that appears in a field or control.

address -- the path to a file (UNC path) or page (URL).

subaddress -- a location within the file or page.

screentip -- the text displayed as a tooltip.

Cursor A reference to a cursor

SQL Variant A data type that stores values of various SQL Server-supported 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 (GUID).

Manually Executing SQL

Access SQL Server Oracle

Executing SQL

Difficult: Create