Datasheet

Data Type Size in
Name Class Bytes Nature of the Data
Ntext Unicode Varies Like the Text data type, this is legacy support
only. In this case, use
nvarchar(max). Variable-
length Unicode character data.
Binary Binary Varies Fixed-length binary data with a maximum length
of 8,000 bytes.
VarBinary Binary Varies Variable-length binary data with a maximum
specified length of 8,000 bytes, but you can use
the
max keyword to indicate it as essentially a
LOB field (up to 2^31 bytes of data).
Image Binary Varies Legacy support only as of SQL Server 2005. Use
varbinary(max) instead.
Table Other Special This is primarily for use in working with result
sets, typically passing one out of a user-defined
function. Not usable as a data type within a table
definition. (You can’t nest tables.)
Sql_variant Other Special This is loosely related to the Variant in VB and
C++. Essentially it’s a container that enables you
to hold most other SQL Server data types in it.
That means you can use this when one column or
function needs to be able to deal with multiple
data types. Unlike VB, using this data type forces
you to cast it
explicitly to convert it to a more spe-
cific data type.
XML Character Varies Defines a character field as being for XML data.
Provides for the validation of data against an
XML schema and the use of special XML-oriented
functions.
Most of these have equivalent data types in other programming languages. For example, an
int in SQL
Server is equivalent to a
Long in Visual Basic, and for most systems and compiler combinations in C++,
is equivalent to an
int.
In general, SQL Server data types work much as you would expect given experience in most other mod-
ern programming languages. Adding numbers yields a sum, but adding strings concatenates them.
SQL Server has no concept of unsigned numeric data types. If you need to allow for
larger numbers than the signed data type allows, consider using a larger signed data
type. If you need to prevent the use of negative numbers, consider using a
CHECK
constraint that restricts valid data to greater than or equal to zero.
13
Being Objective: Re-Examining Objects in SQL Server
04_584340 ch01.qxp 10/18/06 2:11 PM Page 13