Part VI: Access as an Enterprise Platform
1238
TABLE 38.2
Comparison of Access and SQL Server Data Types
Microsoft Access Data Type SQL Server Data Type
Yes/No Bit
Number tinyint, smallint, int, bigint: Very small integers up to very large integers.
Smaller data types use less bytes and occupy less physical space.
real, float: Real numbers and floating point numbers are the same thing.
decimal[(18,0)]: A decimal defaults to 2 decimal places but can be sized up
to 18 bytes with no decimals.
numeric[(18,0)]: Can be a specified length as for decimal.
Currency money, smallmoney
Date/Time datetime, smalldatetime, timestamp
AutoNumber int (with identity property defined)
Text char(10), varchar(50), varchar(n), varchar(MAX): ASCII (8-bit) character set
string variables.
nchar(10), nvarchar(50), nvarchar(n), nvarchar(MAX): Unicode (16-bit) char-
acter set string variables.
char: Fixed-length string, usually short and known sizes, where string is pad-
ded up to fixed length regardless of value.
varchar(50-n): Variable-length strings where no padding is added for shorter
strings.
MAX: Used for extremely large values
Memo text and ntext: Large variable text strings stored in binary form. ntext stores
unicode character set.
OLE Object Image: Intended specifically for storing images in binary form
Attachment No equivalent
Hyperlink No equivalent
Lookup Wizard...: based on
a query or multiple literal
values
No equivalent
No equivalent binary(50), varbinary, varbinary(50), varbinary(MAX)
No equivalent uniqueidentifier
No equivalent xml: XML data type for storing both content and functionality of XML
documents.
No equivalent sql_variant: A variable data type, except it does not allow text, ntext, image,
or timestamp.
