Datasheet

Chapter 1: Being Objective: Re-Examining Objects in SQL Server
Data Type Name Class Size in Bytes Nature of the Data
Table Other Special This is primarily for use in working with
result sets — typically passing one out of
a User-defined Function or as a
parameter for Stored Procedures. Not
usable as a data type within a table
definition (you can’t nest tables).
HierarchyID Other Special Special data type that maintains hierarchy
positioning information. Provides special
functionality specific to hierarchy needs.
Comparisons of depth, parent/child
relationships, and indexing are allowed.
Exact size varies with the number of and
average depth of nodes in the hierarchy.
Sql_variant Other Special This is loosely related to the Variant in
VB and C++. Essentially, it is a container
that allows 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 explicitly cast it in order
to convert it to a more specific data type.
XML Character Varies Defines a character field as being for XML
data. Provides for the validation of data
against an XML Schema as well as the use
of special XML-oriented functions.
Note that compatibility with .NET data types is even stronger than it was before. For example, the new
date and time cross neatly into the .NET world, and the new datetime2 data type also crosses to .NET
more cleanly than the previous
datetime data type does.
Unfortunately, SQL Server continues to have 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.
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. When
you mix the usage or assignment of variables or fields of different data types, a number of types convert
implicitly (or automatically). Most other types can be converted explicitly. (You say specifically what
type you want to convert to.) A few can’t be converted between at all. Figure 1-2 contains a chart that
shows the various possible conversions.
15