|
Table A compares field properties that are similar, but named
differently. Table B compares Desktop and Access data types.
Table A: Comparison of table properties
|
Access
|
SQL Server Desktop
|
Explanation
|
|
Field
name
|
Column
name
|
SQL
Server Desktop uses the term "column" to refer to fields.
|
|
Data
types
|
Data
type
|
Many
SQL Server Desktop data types include specific field size restrictions.
|
|
Field
size
|
Length
|
Access
restricts the field size of a numeric or text field. SQL Server uses
Length to restrict a character field. Numeric restrictions are included
in numeric data types.
|
|
Required
|
Allow
nulls
|
A No
setting in Access, allows Null values. To allow Null values in SQL Server
Desktop, select the Allow Nulls column (see Figure C).
|
|
Table B: Data Types Comparison
|
Access
|
SQL Server Desktop
|
Comment
|
|
Yes/No
|
bit
|
Integers
0 and 1 only
|
|
Number,
Byte
|
tinyint
|
Integer
from 0 through 255
|
|
|
bigint
|
Integer
from -2^63 through 2^63-1
|
|
Number,
Integer
|
smallint
|
Short
integer from -2^15 through 2^15-1
|
|
Number,
Long
|
int
|
Integer
from -2^31 through 2^31-1
|
|
Number,
Decimal
|
decimal
|
Fixed
precision and scale numeric data from -10^38+1 through 10^38-1
|
|
Number,
Double
|
float
|
Double-precision
floating-point value from -1.79E+308 through 1.79E+308
|
|
Number,
Single
|
real
|
Single-precision
floating-point value from -3.40E+38 through 3.40E+38
|
|
Date/Time
|
datetime
|
Valid
date or time (or both). Access supports year values from 100 through
9999. SQL Server Desktop supports year values from 1753 through
9999
|
|
|
smalldatetime
|
Date
and time data from January
1, 1900, through June 6, 2079
|
|
Currency
|
smallmoney
|
Monetary
data values from -214,748.3648 through +214,748.3647, with accuracy to a
ten-thousandth of a monetary unit
|
|
|
money
|
Monetary
data values from -2^63 through 2^63-1
|
|
|
binary
|
Integer
data from -2^63 through 2^63-1
|
|
|
varbinary
|
Variable-length
binary data with a maximum length of 8,000 bytes
|
|
Memo
|
text
|
Any
character, but limited to 2.14 GB
|
|
Text
|
varchar
|
Variable-length
non-unicode data with a maximum of 8,000
characters
|
|
|
char
|
Fixed-length
non-unicode character data with a maximum
length of 8,000 characters
|
|
|
nchar
|
Fixed-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
|
|
|
nvarchar
|
Variable-length
unicode data with a maximum length of 4,000
characters
|
|
OLE
Object
|
image
|
Picture
of object limited to 2.14 GB
|
|
|
sql_variant
|
Can
be used to store different data types
|
|
|
timestamp
|
Generated
binary numbers that are guaranteed to be unique within the current
database (8 bytes)
|
|
|
uniqueidentifer
|
A
globally unique identifier (GUID)
|
|
Desktop also offers more table properties than Access:
- · Tables:
Set or view the Identity column, rename the table, and so on
- · Relationships:
View, create, delete, or modify relationships
- · Index:
View, add, remove, or modify a table's indexes. (Read more on this topic
in the Indexes
sidebar.)
|